unable to get find property of the worksheet function class?

[Liz_I3]

Hello
I need to pull the date from the below string which is in cell A2
XYZ Error Report for data processed on 13/1/2021

I have this below sub but I returns the Unable to get find property of the worksheet function class. Yet I have used it below but instead of the word "on" it was finding "/" and it work great.

Sub CopyFormulaDown()
Dim LastPopulatedRow As Long
Dim ws As Worksheet

Set ws = ThisWorkbook.ActiveSheet

Range("M7").Value = "Processed Date"

Range("M8").Select

ActiveCell.Formula = Mid(ws.Range("A2"), (Application.WorksheetFunction.Find("""on""", Range("A2"), 1) + 1), 256)
'Find last populated row
LastPopulatedRow = Range("A" & Rows.Count).End(xlUp).Row

'Select the rows where formula is to be populated
Range("M8: " & "M" & LastPopulatedRow).FillDown

End Sub


Thank you
L

 

[Fluff]

That is not putting a formula in the cell, it's trying to put the result of the formula in.
What exactly are you trying to do?

 

[Liz_I3]

Hi I want to extract the date portion from the string and put it in Cell M8 then copy it to the last used row of column M

I was hoping it would extract every thing from after the word on. I was using the mid function but was not sure what to do when the date changes in my sample about it is 13/1/2021 but the are no leading zeros so not sure how to tell it to change when it is 13/10/2021 or 3/12/2021

L

 

[Fluff]

Are you trying to put a formula in the cell & fill it down?

 

[Liz_I3]

Yes but the result not the formula. I think I got it going used: ActiveCell.Formula = Mid(ws.Range("A2"), (Application.WorksheetFunction.Find("on", Range("A2"), 1) + 2), 256)

Thanks for looking at it with me

L

 

[Fluff]

That will just put the date from A2 into every single cell from M8 downwards, is that what you want?

 

[Liz_I3]

Yes that is correct. The spread sheet had the date at the end of a string on A2. We needed to take that date and copy it down column M from M8 to all rows that have data (Lastrow). So far it looks like it is giving us what we want. If it fails I will post back.

Thanks
L

 

[Fluff]

Ok, how about

Sub CopyFormulaDown()
Dim LastPopulatedRow As Long
Dim ws As Worksheet

Set ws = ThisWorkbook.ActiveSheet
LastPopulatedRow = Range("A" & Rows.Count).End(xlUp).Row

ws.Range("M7").Value = "Processed Date"

ws.Range("M8:M" & LastPopulatedRow).Value = Split(ws.Range("A2"))(UBound(Split(ws.Range("A2"))))

End Sub