***Official*** Excel Help Corner

[heckmanm]

19 minutes ago, dino259 said:

I am using the formula DATE(RIGHT(F2,2),LEFT(F2,2),MID(F2,3,2)) to convert 6 digits of 112621 to a date.  However it is giving me 11/26/1921 instead of the desired 11/26/2021.  Any thoughts?

If you know it's always 20xx for the year:

=DATE(CONCAT("20",RIGHT(F2,2)),LEFT(F2,2),MID(F2,3,2))

[coopersdad]

Setting up a graph for periodic sales and periodic margins for 2 periods.

I used the graph wizard in excel and have the graph set up, however one of the margin% is using the left axis ($'s) instead of the right axis (%).  How do I change which axis a data set is using?

Make sense?

 

 

[acarey50]

50 minutes ago, coopersdad said:

Setting up a graph for periodic sales and periodic margins for 2 periods.

I used the graph wizard in excel and have the graph set up, however one of the margin% is using the left axis ($'s) instead of the right axis (%).  How do I change which axis a data set is using?

Make sense?

 

 

Select the data series in the Chart and Right Click

Select Format Data Series

Far right option (looks like a chart is the series options)

On that menu, you select if the series should be plotted on the primary or secondary axis

[Runkle]

Somehow I did something that works, but, I can't seem to replicate it...

 

On Sheet1, I have a range of cells with a name, like Cell_Range, that refers to A1:A100 on Sheet1 only

On Sheet 2, I have a range of cells with the name Cell_Range, that refers to A1:A100 on Sheet2 only.

I cannot seem to recreate this with another range, every time I define a range, it applies it to the whole workbook instead of the sheet. 

I would like to create a second range Range_Two on Sheet1, define it on Sheet1, and use it in formulas on Sheet1. But when I go to Sheet2 to create a second range with the name, the formulas on Sheet2 look at Sheet1 for the cells instead of the range on that sheet.

 

I don't know how I defined a Named Range to apply to one sheet at a time only, but I did, now I'm trying to do it again, any hints?

 

 

(I have a list of exclusions in the range that apply to a formula, I want each sheet to reference its own independent list of exclusions so I can compare the options)

 

Edit: I think I solved this, the problem was that defining the scope of a named range is not an option on the Mac version of Excel. I opened the spreadsheet on a PC, edited the scope from workbook to sheet1, and then saved it and re-opened it on a Mac. Once back on the Mac I can use range names by sheet. When I go into the Define Names dialog, each selection references the sheet-only on the Mac. Weird this isn't available otherwise.

Edited November 23, 2021 by Runkle

[Jayrok]

I recorded a macro that pastes a date in the same cell beneath another date. 

Range("E37").Select

ActiveCell.FormulaR1C1 = "10/29/2021 " & Chr(10) & "12/1/2021"

 

Basically it selects a cell, double clicks and "end" to go to the end of the date value, then add a space... and then Alt+Enter to create space below the date in the cell... and then paste the contents of the clipboard (or what I had copied before running the shortcut).  

The cell range isn't important, e.g. it isn't always going to be E37.  I want to click in a cell, any cell and hit the shortcut key and have it go to the end of the cell value (it will always be one date but not always the same date (not always 10/29/2021)... insert one space and then Alt+Enter... and then paste.  The pasted date will be different each time so whatever is on the clipboard is what I need.  

The above example works but instead of "10/29/2021" and "12/1/2021" is there a wildcard for numbers/dates that I can use to that the macro will go the the end of whatever date is in the particular cell?  and when it pastes I need it to paste whatever is copied to clipboard at the time, not "12/1/2021".  

Is there a way I can achieve this?  Thanks in advance for any assistance.

[Ned]

36 minutes ago, Jayrok said:

I recorded a macro that pastes a date in the same cell beneath another date. 

Range("E37").Select

ActiveCell.FormulaR1C1 = "10/29/2021 " & Chr(10) & "12/1/2021"

 

Basically it selects a cell, double clicks and "end" to go to the end of the date value, then add a space... and then Alt+Enter to create space below the date in the cell... and then paste the contents of the clipboard (or what I had copied before running the shortcut).  

The cell range isn't important, e.g. it isn't always going to be E37.  I want to click in a cell, any cell and hit the shortcut key and have it go to the end of the cell value (it will always be one date but not always the same date (not always 10/29/2021)... insert one space and then Alt+Enter... and then paste.  The pasted date will be different each time so whatever is on the clipboard is what I need.  

The above example works but instead of "10/29/2021" and "12/1/2021" is there a wildcard for numbers/dates that I can use to that the macro will go the the end of whatever date is in the particular cell?  and when it pastes I need it to paste whatever is copied to clipboard at the time, not "12/1/2021".  

Is there a way I can achieve this?  Thanks in advance for any assistance.

Is the copied data from the same workbook?  Relying on the clipboard makes this more complicated.

[Jayrok]

36 minutes ago, Ned said:

Is the copied data from the same workbook?  Relying on the clipboard makes this more complicated.

 

It is all within the same worksheet.  Same row a few columns over.  

[Ned]

On 12/17/2021 at 4:01 PM, Jayrok said:

On 12/17/2021 at 3:24 PM, Ned said:

Is the copied data from the same workbook?  Relying on the clipboard makes this more complicated.

 

It is all within the same worksheet.  Same row a few columns over.  

 

This will work by selecting two cells and running the macro. You have to click the cell you want to 'copy' and then shift click the destination cell.  Run the macro and it'll add the copy cell to the bottom of your destination cell.  It reads the cells in the order you clicked them, so make sure you click the copy first.

It only works if you've selected 2 cells.

Sub Jayrok()
    Dim cell As Range, newVal As String
    If Selection.Cells.Count = 2 Then
        For Each cell In Selection
            If newVal = vbNullString Then
                newVal = cell.Value
            Else
                newVal = cell.Value & " " & Chr(10) & newVal
            End If
        Next cell
        ActiveCell.Value = newVal
    End If
End Sub

 

 

[Jayrok]

1 hour ago, Ned said:

 

This will work by selecting two cells and running the macro. You have to click the cell you want to 'copy' and then shift click the destination cell.  Run the macro and it'll add the copy cell to the bottom of your destination cell.  It reads the cells in the order you clicked them, so make sure you click the copy first.

It only works if you've selected 2 cells.

Sub Jayrok()
    Dim cell As Range, newVal As String
    If Selection.Cells.Count = 2 Then
        For Each cell In Selection
            If newVal = vbNullString Then
                newVal = cell.Value
            Else
                newVal = cell.Value & " " & Chr(10) & newVal
            End If
        Next cell
        ActiveCell.Value = newVal
    End If
End Sub

 

 

 

Thanks, Ned.  This works great and is close.  This link is a snapshot of what I'm trying to do.

The middle two columns aren't important.  One of my managers has to manually input dates on a large spreadsheet and would like to automate this particular step if possible.  One good reason is to try and limit the human tendency to fat-finger, etc.  

In column D, it's the second date that he needs to select and copy (not the entire cell).  Column A is where he needs to insert that copied date into the circled slot in the appropriate cell.  The date will not go into each cell in column A, just a few (and they are random each time).  The copied date is also random.  The "copy" portion isn't necessarily part of the macro because he may copy D2 and paste that date into A2, A3, A7, A15 and so forth.  Then, he'll copy a different date in, say, D7 and paste it into the appropriate slot (as a second date) in A16.  It's random.

So the main objective is he copies some date in some cell in column D.  Then he double clicks into a cell in column A.  He clicks the "end" key to move the cursor to the end of the date in that cell.  Then he adds a space (this is necessary because the query that runs this data fails to recognize the date if he doesn't add at least one space.  Then he does the Chr(10) to allow the pasted date to go under the original date in the cell.  Then he pastes the date.  

I believe if we have a macro where he can click a cell and hit ctrl-h (or whatever short cut) and have it do the steps above (except the copy, as he'll need to copy different dates from various cells in column D)... he would be able to insert his "copied" date into several cells, one after the other (not at the same time).  

This would ensure that the date would be input properly (not fat-fingered) and in the correct alignment/format (i.e. the correct spacing) and allow us to run queries, etc., without errors.  And according to him, it will save him a lot of time.  

As for the second date copy (column D), I was thinking maybe start after x characters or start RIGHT and go back x characters.  The problem with that is it could be 8, 9, or 10 depending on the date.  Or start to search after 10 characters, then select the value.  

Thanks

[Ned]

52 minutes ago, Jayrok said:

 

Thanks, Ned.  This works great and is close.  This link is a snapshot of what I'm trying to do.

The middle two columns aren't important.  One of my managers has to manually input dates on a large spreadsheet and would like to automate this particular step if possible.  One good reason is to try and limit the human tendency to fat-finger, etc.  

In column D, it's the second date that he needs to select and copy (not the entire cell).  Column A is where he needs to insert that copied date into the circled slot in the appropriate cell.  The date will not go into each cell in column A, just a few (and they are random each time).  The copied date is also random.  The "copy" portion isn't necessarily part of the macro because he may copy D2 and paste that date into A2, A3, A7, A15 and so forth.  Then, he'll copy a different date in, say, D7 and paste it into the appropriate slot (as a second date) in A16.  It's random.

So the main objective is he copies some date in some cell in column D.  Then he double clicks into a cell in column A.  He clicks the "end" key to move the cursor to the end of the date in that cell.  Then he adds a space (this is necessary because the query that runs this data fails to recognize the date if he doesn't add at least one space.  Then he does the Chr(10) to allow the pasted date to go under the original date in the cell.  Then he pastes the date.  

I believe if we have a macro where he can click a cell and hit ctrl-h (or whatever short cut) and have it do the steps above (except the copy, as he'll need to copy different dates from various cells in column D)... he would be able to insert his "copied" date into several cells, one after the other (not at the same time).  

This would ensure that the date would be input properly (not fat-fingered) and in the correct alignment/format (i.e. the correct spacing) and allow us to run queries, etc., without errors.  And according to him, it will save him a lot of time.  

As for the second date copy (column D), I was thinking maybe start after x characters or start RIGHT and go back x characters.  The problem with that is it could be 8, 9, or 10 depending on the date.  Or start to search after 10 characters, then select the value.  

Thanks

I always find these sorta things fun.     You can run this macro any time you want, but it relies on something being in the clipboard (copied).  Beware that you cannot use Undo....

To use it:

1. Copy the cell you want to use as your data source (D2 in your example).
2. Click the cell(s) you want the second date inserted into (A2, A3, A5, etc).  It'll loop through your entire selection; you can run it for a single cell or a range of cells. 
3. You can re-run it as many times as you want as long as the data is still in the clipboard.

I added a few error checks since it sounded like data integrity was important to what you're doing.  It will show a warning for any of these 3 conditions and not do anything else. 

1. If the macro is run without anything in the clipboard (i.e. someone ran it without doing a copy first)
2. There's no carriage return (chr(10).
3. Data was found after the carriage return, but it wasn't a date.

Sub Jayrok_v2()
    Dim clippy As Object, cpData As Variant, cpDate As String, err As String
    
    'save data from the clipboard
    Set clippy = CreateObject("HtmlFile")
    cpData = clippy.parentWindow.clipboardData.GetData("text")
    
    'don't do anything if nothing was copied
    If IsNull(cpData) Then
        err = MsgBox("No data to paste!", vbCritical)
    Else 'data found in clipboard; test data before adding to destination
        cpDate = Replace(Mid(cpData, InStr(cpData, Chr(10)) + 1, 10), """", "") 'parse the second date after chr(10)
        If cpDate = "" Then
            'the copied cell didn't have a second date
            err = MsgBox("No date found!", vbCritical)
        Else
            'is it a real date?
            If IsDate(cpDate) Then
                'data looks good; add to destination cells
                For Each cell In Selection
                    cell.Value = cell.Value & " " & Chr(10) & cpDate
                Next cell
            Else
                'copied cell has a chr(10), but what follows isn't a date
                err = MsgBox("Copied data is not a date!", vbCritical)
            End If
        End If
    End If
End Sub

 

 

 

 

[Jayrok]

32 minutes ago, Ned said:

I always find these sorta things fun.     You can run this macro any time you want, but it relies on something being in the clipboard (copied).  Beware that you cannot use Undo....

To use it:

1. Copy the cell you want to use as your data source (D2 in your example).
2. Click the cell(s) you want the second date inserted into (A2, A3, A5, etc).  It'll loop through your entire selection; you can run it for a single cell or a range of cells. 
3. You can re-run it as many times as you want as long as the data is still in the clipboard.

I added a few error checks since it sounded like data integrity was important to what you're doing.  It will show a warning for any of these 3 conditions and not do anything else. 

1. If the macro is run without anything in the clipboard (i.e. someone ran it without doing a copy first)
2. There's no carriage return (chr(10).
3. Data was found after the carriage return, but it wasn't a date.

Sub Jayrok_v2()
    Dim clippy As Object, cpData As Variant, cpDate As String, err As String
    
    'save data from the clipboard
    Set clippy = CreateObject("HtmlFile")
    cpData = clippy.parentWindow.clipboardData.GetData("text")
    
    'don't do anything if nothing was copied
    If IsNull(cpData) Then
        err = MsgBox("No data to paste!", vbCritical)
    Else 'data found in clipboard; test data before adding to destination
        cpDate = Replace(Mid(cpData, InStr(cpData, Chr(10)) + 1, 10), """", "") 'parse the second date after chr(10)
        If cpDate = "" Then
            'the copied cell didn't have a second date
            err = MsgBox("No date found!", vbCritical)
        Else
            'is it a real date?
            If IsDate(cpDate) Then
                'data looks good; add to destination cells
                For Each cell In Selection
                    cell.Value = cell.Value & " " & Chr(10) & cpDate
                Next cell
            Else
                'copied cell has a chr(10), but what follows isn't a date
                err = MsgBox("Copied data is not a date!", vbCritical)
            End If
        End If
    End If
End Sub

 

 

 

 

Stuff like this is fun for me too but I'm not as advanced as I'd like to be... but I'm always learning.

I'm getting the No date found error when I copy the source cell and run the macro.  There are two dates in the cell and I tried to add space(s) and remove all spaces between the two dates.  It doesn't recognize the second date on the clipboard.

I also tried only selecting the second date and copying it to the clipboard but get same error.

[Ned]

2 minutes ago, Jayrok said:

Stuff like this is fun for me too but I'm not as advanced as I'd like to be... but I'm always learning.

I'm getting the No date found error when I copy the source cell and run the macro.  There are two dates in the cell and I tried to add space(s) and remove all spaces between the two dates.  It doesn't recognize the second date on the clipboard.

I also tried only selecting the second date and copying it to the clipboard but get same error.

It's looking for the chr(10) between the two dates.

Here's the snippet of code: cpDate = Replace(Mid(cpData, InStr(cpData, Chr(10)) + 1, 10), """", "")

You can change the chr(10) to whatever's between the two dates.

[Jayrok]

9 minutes ago, Ned said:

It's looking for the chr(10) between the two dates.

Here's the snippet of code: cpDate = Replace(Mid(cpData, InStr(cpData, Chr(10)) + 1, 10), """", "")

You can change the chr(10) to whatever's between the two dates.

So I understand, If there is a date in the cell and then he puts another date in the cell, he will click in the cell and put his cursor at the end of the first date.  He won't add any spaces after... he will just click Alt+enter and then he'll type the second date.  

Given that, will the code need chr(10)?  or how many spaces does that create between the two dates?  I'm not sure what to put there.  If he added one space after the first date and then did the Alt+enter, what would need to be in place of chr(10) in the macro?

[Ned]

Just now, Jayrok said:

So I understand, If there is a date in the cell and then he puts another date in the cell, he will click in the cell and put his cursor at the end of the first date.  He won't add any spaces after... he will just click Alt+enter and then he'll type the second date.  

Given that, will the code need chr(10)?  or how many spaces does that create between the two dates?  I'm not sure what to put there.  If he added one space after the first date and then did the Alt+enter, what would need to be in place of chr(10) in the macro?

 

D2 should have something between the dates, or the code is going to get more complicated.  I had assumed D2 already had a space and a chr(10) in it.  What is typically separating the dates in D2?

 

 

 

 

[Jayrok]

1 minute ago, Ned said:

 

D2 should have something between the dates, or the code is going to get more complicated.  I had assumed D2 already had a space and a chr(10) in it.  What is typically separating the dates in D2?

 

 

 

 

 

That is correct.  D2 has one date. He will enter the second date by clicking in the cell and then going to the end of that first date... add one space and then a chr(10).  Then he types the second date.  So just one space and then alt+enter.  I made sure the dates in D2 are input this way and it still gets the No date found error.  

[Ned]

58 minutes ago, Jayrok said:

 

That is correct.  D2 has one date. He will enter the second date by clicking in the cell and then going to the end of that first date... add one space and then a chr(10).  Then he types the second date.  So just one space and then alt+enter.  I made sure the dates in D2 are input this way and it still gets the No date found error.  

Hmm, so the code is right.  Can you put an example in this google sheet?  I'll copy to excel and test.

 

https://docs.google.com/spreadsheets/d/1BOQmdtnBeIWFs-NGuFEsgNG2obuV1FgSDDJJYk-7ROk/edit?usp=sharing

[Jayrok]

12 minutes ago, Ned said:

Hmm, so the code is right.  Can you put an example in this google sheet?  I'll copy to excel and test.

 

https://docs.google.com/spreadsheets/d/1BOQmdtnBeIWFs-NGuFEsgNG2obuV1FgSDDJJYk-7ROk/edit?usp=sharing

I put 12/20/2021 in D2 the way he will enter the data. 

[Ned]

2 minutes ago, Jayrok said:

I put 12/20/2021 in D2 the way he will enter the data. 

Hmmm it works for me.

Are you copying D2 and then selecting a cell in column A and then running the macro?

[Jayrok]

1 minute ago, Ned said:

Hmmm it works for me.

Are you copying D2 and then selecting a cell in column A and then running the macro?

I copy cell D2 then select A2, then run the macro.

[Ned]

4 minutes ago, Jayrok said:

I copy cell D2 then select A2, then run the macro.

I just noticed that if you go to Macros - Run Macro, it clears the clipboard.

Assign a keyboard shortcut to the macro and then try again by using the shortcut. 

Here it is working

[Jayrok]

1 minute ago, Ned said:

I just noticed that if you go to Macros - Run Macro, it clears the clipboard.

Assign a keyboard shortcut to the macro and then try again by using the shortcut. 

Here it is working

That looks perfect. I have assigned ctrl+m as a shortcut and have been using that all along.  I haven't ran the macro from macros.  I copy the source cell, then select destination cell, then shortcut to run macro.  Not sure why I'm getting the error.

Does it matter of the copied date has 8,9 or 10 characters?  

[Ned]

5 minutes ago, Jayrok said:

That looks perfect. I have assigned ctrl+m as a shortcut and have been using that all along.  I haven't ran the macro from macros.  I copy the source cell, then select destination cell, then shortcut to run macro.  Not sure why I'm getting the error.

Does it matter of the copied date has 8,9 or 10 characters?  

It shouldn't matter as long as it's some variation of mm/dd/yy. 

Can you do the same steps you've been doing, but use this code?  Copy/paste G2:H2 into the googlesheeet.

Sub Jayrok_v2()
    Dim clippy As Object, cpData As Variant, cpDate As String, err As String
    
    'save data from the clipboard
    Set clippy = CreateObject("HtmlFile")
    cpData = clippy.parentWindow.clipboardData.GetData("text")
    
    'don't do anything if nothing was copied
    If IsNull(cpData) Then
        err = MsgBox("No data to paste!", vbCritical)
    Else 'data found in clipboard; test data before adding to destination
        cpDate = Replace(Mid(cpData, InStr(cpData, Chr(10)) + 1, 10), """", "") 'parse the second date after chr(10)
        
        'debugging only
        Range("G2") = cpData
        Range("H2") = cpDate
        
        If cpDate = "" Then
            'the copied cell didn't have a second date
            err = MsgBox("No date found!", vbCritical)
        Else
            'is it a real date?
            If IsDate(cpDate) Then
                'data looks good; add to destination cells
                For Each cell In Selection
                    cell.Value = cell.Value & " " & Chr(10) & cpDate
                Next cell
            Else
                'copied cell has a chr(10), but what follows isn't a date
                err = MsgBox("Copied data is not a date!", vbCritical)
            End If
        End If
    End If
End Sub

 

[Jayrok]

24 minutes ago, Ned said:

It shouldn't matter as long as it's some variation of mm/dd/yy. 

Can you do the same steps you've been doing, but use this code?  Copy/paste G2:H2 into the googlesheeet.

Sub Jayrok_v2()
    Dim clippy As Object, cpData As Variant, cpDate As String, err As String
    
    'save data from the clipboard
    Set clippy = CreateObject("HtmlFile")
    cpData = clippy.parentWindow.clipboardData.GetData("text")
    
    'don't do anything if nothing was copied
    If IsNull(cpData) Then
        err = MsgBox("No data to paste!", vbCritical)
    Else 'data found in clipboard; test data before adding to destination
        cpDate = Replace(Mid(cpData, InStr(cpData, Chr(10)) + 1, 10), """", "") 'parse the second date after chr(10)
        
        'debugging only
        Range("G2") = cpData
        Range("H2") = cpDate
        
        If cpDate = "" Then
            'the copied cell didn't have a second date
            err = MsgBox("No date found!", vbCritical)
        Else
            'is it a real date?
            If IsDate(cpDate) Then
                'data looks good; add to destination cells
                For Each cell In Selection
                    cell.Value = cell.Value & " " & Chr(10) & cpDate
                Next cell
            Else
                'copied cell has a chr(10), but what follows isn't a date
                err = MsgBox("Copied data is not a date!", vbCritical)
            End If
        End If
    End If
End Sub

 

 

So this is bizarre.  I ran this on my test sheet on my personal computer, the one I screenshot for you, and it works.  On my actual work datasheet, it doesn't work.  I copy/pasted the code from here to my workbook in a new macro so it's all the same. 

For the debugging data, my worksheet puts nothing in the cell range for cpData and cpDate.  It just errors out with the No date found msg.  

cpDate is a text string so it shouldn't matter what the cell format type is for the worksheet.  

[Ned]

31 minutes ago, Jayrok said:

 

So this is bizarre.  I ran this on my test sheet on my personal computer, the one I screenshot for you, and it works.  On my actual work datasheet, it doesn't work.  I copy/pasted the code from here to my workbook in a new macro so it's all the same. 

For the debugging data, my worksheet puts nothing in the cell range for cpData and cpDate.  It just errors out with the No date found msg.  

cpDate is a text string so it shouldn't matter what the cell format type is for the worksheet.  

OK, that's really weird.  Try moving this "Range("G2") = cpData" up to the top before it drops into the main IF/THEN/ELSE.

For whatever reason the cpDate variable assignment isn't working.

'save data from the clipboard
    Set clippy = CreateObject("HtmlFile")
    cpData = clippy.parentWindow.clipboardData.GetData("text")
    Range("G2") = cpData

 

 

[Jayrok]

13 minutes ago, Ned said:

OK, that's really weird.  Try moving this "Range("G2") = cpData" up to the top before it drops into the main IF/THEN/ELSE.

For whatever reason the cpDate variable assignment isn't working.

'save data from the clipboard
    Set clippy = CreateObject("HtmlFile")
    cpData = clippy.parentWindow.clipboardData.GetData("text")
    Range("G2") = cpData

 

 

Same thing.  No date found. 

[Ned]

2 minutes ago, Jayrok said:

Same thing.  No date found. 

What did it put in G2?

[Jayrok]

2 minutes ago, Ned said:

What did it put in G2?

nothing

[Ned]

1 minute ago, Jayrok said:

nothing

 

I wonder if its a 365 issue.  My home PC is still Excel 2013.  What are you using?

I can test it tomorrow at work with 365.

[Jayrok]

6 minutes ago, Ned said:

What did it put in G2?

Ok, I'm trying to narrow this down.  I emailed my workbook from my work laptop to my desktop and changed nothing.  The macro works.  So it isn't the code.

The only difference is when I'm on my work laptop, I'm using VDI to connect to our work network.  The workbook has many macros and many that I've built with no issues.  This is a first.  I don't see how VDI has anything to do with it.  

[Jayrok]

1 minute ago, Ned said:

 

I wonder if its a 365 issue.  My home PC is still Excel 2013.  What are you using?

I can test it tomorrow at work with 365.

I bet that's it.  365 at work and 2016 on my desktop.  I work from home and my laptop is right beside my desktop.  

So could it be in the  clippy.parentWindow.clipboardData.GetData("text") line?  

[Ned]

1 minute ago, Jayrok said:

I bet that's it.  365 at work and 2016 on my desktop.  I work from home and my laptop is right beside my desktop.  

So could it be in the  clippy.parentWindow.clipboardData.GetData("text") line?  

Yeah I'm willing to bet the clipboard is being used differently with 365.  I'll dig into it tomorrow when I'm in between stuff.

[Ned]

On 12/20/2021 at 3:55 PM, Jayrok said:

I bet that's it.  365 at work and 2016 on my desktop.  I work from home and my laptop is right beside my desktop.  

So could it be in the  clippy.parentWindow.clipboardData.GetData("text") line?  

Sorry it took me a bit to get back to this.  It definitely looks like a 365 issue.  I recoded in 365; try this:

Sub Jayrok_v3()
    Dim clippy As New MSForms.DataObject, cpData As Variant, cpDate As String, err As String
    
    'save data from the clipboard
    clippy.GetFromClipboard
    On Error Resume Next
    cpData = clippy.GetText()
    
    'don't do anything if nothing was copied
    If IsNull(cpData) Then
        err = MsgBox("No data to paste!", vbCritical)
    Else 'data found in clipboard; test data before adding to destination
        cpDate = Replace(Mid(cpData, InStr(cpData, Chr(10)) + 1, 10), """", "") 'parse the second date after chr(10)
        
        If cpDate = "" Then
            'the copied cell didn't have a second date
            err = MsgBox("No date found!", vbCritical)
        Else
            'is it a real date?
            If IsDate(cpDate) Then
                'data looks good; add to destination cells
                For Each cell In Selection
                    cell.Value = cell.Value & " " & Chr(10) & cpDate
                Next cell
            Else
                'copied cell has a chr(10), but what follows isn't a date
                err = MsgBox("Copied data is not a date!", vbCritical)
            End If
        End If
    End If
End Sub

 

[Jayrok]

15 hours ago, Ned said:

Sorry it took me a bit to get back to this.  It definitely looks like a 365 issue.  I recoded in 365; try this:

Sub Jayrok_v3()
    Dim clippy As New MSForms.DataObject, cpData As Variant, cpDate As String, err As String
    
    'save data from the clipboard
    clippy.GetFromClipboard
    On Error Resume Next
    cpData = clippy.GetText()
    
    'don't do anything if nothing was copied
    If IsNull(cpData) Then
        err = MsgBox("No data to paste!", vbCritical)
    Else 'data found in clipboard; test data before adding to destination
        cpDate = Replace(Mid(cpData, InStr(cpData, Chr(10)) + 1, 10), """", "") 'parse the second date after chr(10)
        
        If cpDate = "" Then
            'the copied cell didn't have a second date
            err = MsgBox("No date found!", vbCritical)
        Else
            'is it a real date?
            If IsDate(cpDate) Then
                'data looks good; add to destination cells
                For Each cell In Selection
                    cell.Value = cell.Value & " " & Chr(10) & cpDate
                Next cell
            Else
                'copied cell has a chr(10), but what follows isn't a date
                err = MsgBox("Copied data is not a date!", vbCritical)
            End If
        End If
    End If
End Sub

 

 

Thanks, Ned. No worries.  I get a compile error:  User-defined type not defined at "clippy As New MSForms.DataObject"

 

[Ned]

3 minutes ago, Jayrok said:

 

Thanks, Ned. No worries.  I get a compile error:  User-defined type not defined at "clippy As New MSForms.DataObject"

 

Shoot, I forgot to tell you.  This requires a Reference to be included.

Got to VBA and select Tools - References.  Look for Microsoft Forms 2.0 Object Library.  Check that one off and try again.

It'll stay with the workbook, so no worries about guiding the boss through that.

[Jayrok]

5 minutes ago, Ned said:

Shoot, I forgot to tell you.  This requires a Reference to be included.

Got to VBA and select Tools - References.  Look for Microsoft Forms 2.0 Object Library.  Check that one off and try again.

It'll stay with the workbook, so no worries about guiding the boss through that.

 

Bingo!  works like a champ.  Thank you so much, Ned.  You are a gentleman and a scholar.  Merry Christmas to you and yours!

[culdeus]

Not sure if excel is the right tool for this.  I have two datasets of >1Million rows each.

Both data sets have a unique identifier for each row.  

Not every identifier shows up in both sets, but most will.

My goal is to do the following

I want to search both dataset A and dataset B for a list of identifiers and return all values associated with those ID.

The issue I am running into is that excel caps out at 1M rows so I can't simply have Sheet A, Sheet B, and a bunch of Vlookups to deal with the merging.