unable to get find property of the worksheet function class?

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
645
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2002
Messages
645
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
Are you trying to put a formula in the cell & fill it down?
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
645
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
That will just put the date from A2 into every single cell from M8 downwards, is that what you want?
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
645
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,791
Messages
5,638,340
Members
417,021
Latest member
moon miner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top