What's new
Fantasy Football - Footballguys Forums

Welcome to Our Forums. Once you've registered and logged in, you're primed to talk football, among other topics, with the sharpest and most experienced fantasy players on the internet.

***Official*** Excel Help Corner (3 Viewers)

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?
Click to expand...
If you know it's always 20xx for the year:

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

 
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?

 
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?
Click to expand...
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

 
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.

 
Last edited by a moderator:
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.

 
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.
Click to expand...
Is the copied data from the same workbook?  Relying on the clipboard makes this more complicated.

 
Jayrok said:
Ned said:
Is the copied data from the same workbook?  Relying on the clipboard makes this more complicated.
Click to expand...


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


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.

Code: 
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
 
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
Click to expand...



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

 
  • Thanks
Reactions: Ned
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
Click to expand...
I always find these sorta things fun.  :nerd:    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.
Code: 
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
 
Ned said:
I always find these sorta things fun.  :nerd:    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
Click to expand...

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.

 
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.
Click to expand...
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.

 
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.
Click to expand...
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?

 
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?
Click to expand...


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?

 
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?
Click to expand...


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.  

 
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.  
Click to expand...
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

 
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
Click to expand...
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?  

 
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?  
Click to expand...
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.

Code: 
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
 
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
Click to expand...



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.  

 
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.  
Click to expand...
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

 
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
Click to expand...
Same thing.  No date found. 

 
Ned said:
What did it put in G2?
Click to expand...
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.  

 
  • Thinking
Reactions: Ned
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.
Click to expand...
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?  

 
  • Smile
Reactions: Ned
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?  
Click to expand...
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.

 
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?  
Click to expand...
Sorry it took me a bit to get back to this.  It definitely looks like a 365 issue.  I recoded in 365; try this:

Code: 
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
 
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
Click to expand...



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

 
Jayrok said:
Thanks, Ned. No worries.  I get a compile error:  User-defined type not defined at "clippy As New MSForms.DataObject"
Click to expand...
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.

 
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.
Click to expand...


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

 
  • Love
Reactions: Ned
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.  

 
I have to submit a .csv file to the bank on a weekly basis going forward. They want the date to be listed as 03172023. I do that, set the format as "Text" and it looks good. But when I reopen the .csv, that formating is gone and it defauls back to "General" dropping the leading zero. It also doesn't remember column widths, so long numbers are showing up as forumlas when reopened. Any thoughts? The bank of course has no advice.
 
Nick Vermeil said:
I have to submit a .csv file to the bank on a weekly basis going forward. They want the date to be listed as 03172023. I do that, set the format as "Text" and it looks good. But when I reopen the .csv, that formating is gone and it defauls back to "General" dropping the leading zero. It also doesn't remember column widths, so long numbers are showing up as forumlas when reopened. Any thoughts? The bank of course has no advice.
Click to expand...
When you reopen the CSV, are you opening the file in Text Editor or Excel? Text editor will show what is actually there; Excel may do a reformat when you open. If it's dropped the zero in the text file, put a ' in front of each date before saving.
 
Hey All,

My wife has an Excel spreadsheet. She has many sub categories. She is setting a filter at say row 3, across the row. Then she wants to add a row filter at say row 35 and row 65, without losing any of the original row filters.

How can you have multiple rows with active filters? Right now, it seems if row 3 is filtered, when you want to filter another row, it negates the original row filter.

thx
 
Questiom... trying to run the vbd excel app for a cheat sheet but I keep getting "Security Risk Microsoft has blocked macros from running because the source of the file is untrusted" "A potentially dangerous macro has been blocked" error message after trying to Get latest projections. I enabled Macros in the settings, but i still get the same error when I retry or close and re open
 
simsarge said:
Questiom... trying to run the vbd excel app for a cheat sheet but I keep getting "Security Risk Microsoft has blocked macros from running because the source of the file is untrusted" "A potentially dangerous macro has been blocked" error message after trying to Get latest projections. I enabled Macros in the settings, but i still get the same error when I retry or close and re open
Click to expand...

Turn on macros in Backstage view​

Another way to enable macros for a specific workbook is via the Office Backstage view. Here's how:

  1. Click the File tab, and then click Info in the left menu.
  2. In the Security Warning area, click Enable Content > Enable All Content.
As with the previous method, your workbook will become a trusted document.
 
culdeus said:
What stupid setting is it that it tries to guess what you want to fill down below a cell?
Click to expand...
Not 100% sure what you are talking about, but I would look under the Options/Advanced menu, probably one of the check boxes under "Editing Options".
 
i'm stumped. google search says no one has the answer or it's not possible. ExcelGuys are smarter than google, right?

i have a cell in a worksheet that has data validation so it behaves as a simple drop down selection box. The worksheet is protected, but the cells in question are not locked, so users can type in or copy and format as they please in these cells. When i copy the cell down to other lines, all values and formats follow, but not the data validation. If I unprotect the cell and do the same, the data validation does copy down correctly.

is there a setting i'm missing to allow the data validation to also copy down to the new cells while the worksheet is protected?

(culdeus, a few months late, but that stupid function is called "flash fill")
 
Pigskin Fanatic said:
i'm stumped. google search says no one has the answer or it's not possible. ExcelGuys are smarter than google, right?

i have a cell in a worksheet that has data validation so it behaves as a simple drop down selection box. The worksheet is protected, but the cells in question are not locked, so users can type in or copy and format as they please in these cells. When i copy the cell down to other lines, all values and formats follow, but not the data validation. If I unprotect the cell and do the same, the data validation does copy down correctly.

is there a setting i'm missing to allow the data validation to also copy down to the new cells while the worksheet is protected?

(culdeus, a few months late, but that stupid function is called "flash fill")
Click to expand...

When you set up the data validation, did you lock down the range (ie put the $ before the column and row)? If not, then when you copy it down you likely lose the reference.
 
Pigskin Fanatic said:
i'm stumped. google search says no one has the answer or it's not possible. ExcelGuys are smarter than google, right?

i have a cell in a worksheet that has data validation so it behaves as a simple drop down selection box. The worksheet is protected, but the cells in question are not locked, so users can type in or copy and format as they please in these cells. When i copy the cell down to other lines, all values and formats follow, but not the data validation. If I unprotect the cell and do the same, the data validation does copy down correctly.

is there a setting i'm missing to allow the data validation to also copy down to the new cells while the worksheet is protected?

(culdeus, a few months late, but that stupid function is called "flash fill")
Click to expand...
I'm about a 4/10 when it comes to Excel, but I pasted your post into ChatGPT and got the following back. Does it help? ... I have no idea, as it's all foreign to me.

It seems like you're encountering a specific behavior of Excel where copying cells with data validation to other cells does not carry over the data validation rules when the worksheet is protected, even if the cells themselves are not locked. This can be somewhat limiting because it prevents the data validation from being applied to new cells via copying, which is a common task in Excel.

Unfortunately, there's no direct setting in Excel that allows data validation to be copied down to new cells while the worksheet is protected. Data validation rules are generally maintained when copying cells in an unprotected sheet, but protection constraints can interfere with this behavior.

Here are a few approaches you might consider to work around this issue:

  1. Use a Macro to Copy Data Validation: If you're comfortable with VBA, you can write a macro that temporarily unprotects the sheet, copies the data validation, and then re-protects the sheet. This approach requires careful consideration of security and protection settings. Ensure macros are allowed to run by the users.
  2. Pre-apply Data Validation to a Larger Range: Before protecting the sheet, apply the data validation to all the cells in the column or range where you expect data to be entered or copied. This way, even if the sheet is protected, the cells will already have the necessary validation applied.
  3. Adjust Protection Settings: When you protect the worksheet, ensure that you check the option that allows users to edit objects or scenarios, depending on what Excel version you're using. Sometimes, tweaking these settings can allow for more flexibility while still keeping the sheet protected.
  4. Manual Application of Data Validation for Users: Educate users on how to apply data validation themselves if they add new rows or cells that need it. This is more of a workaround than a solution and might not be feasible depending on the users' Excel proficiency.
  5. Unprotect and Reprotect Through User Actions: Consider creating a simple interface (such as a button with a macro) that users can click to unprotect the sheet, perform their copying or data entry, and then reprotect the sheet. This would automate the steps and reduce the risk of leaving the sheet unprotected accidentally.
Click to expand...
 
bryhamm said:
Pigskin Fanatic said:
i'm stumped. google search says no one has the answer or it's not possible. ExcelGuys are smarter than google, right?

i have a cell in a worksheet that has data validation so it behaves as a simple drop down selection box. The worksheet is protected, but the cells in question are not locked, so users can type in or copy and format as they please in these cells. When i copy the cell down to other lines, all values and formats follow, but not the data validation. If I unprotect the cell and do the same, the data validation does copy down correctly.

is there a setting i'm missing to allow the data validation to also copy down to the new cells while the worksheet is protected?

(culdeus, a few months late, but that stupid function is called "flash fill")
Click to expand...

When you set up the data validation, did you lock down the range (ie put the $ before the column and row)? If not, then when you copy it down you likely lose the reference.
Click to expand...
thanks. i checked and it's there with the explicit reference w/ $. But this is part of what has be stumped. it works fine if i unprotect the sheet, so the reference probably isn't the issue.
 
You must log in or register to reply here.

Similar threads

ragincajun
Excel AI!!!!
Replies
26
Views
987
culdeus
culdeus
L
Work Efficiency & Organization
Replies
15
Views
554
MAC_32
MAC_32
GordonGekko
What Are Harsh Truths About Real Life That You Would Prefer To Just Ignore ? (8/30)
Replies
5
Views
443
bigbottom
bigbottom

Users who are viewing this thread

Total: 4 (members: 0, guests: 4)
Top