VBA code to find cell based on another cells value

[VBAdonkey]

Hello,

I have no formal coding training or background and am attempting to teach myself a bit of VBA as we use a lot of spreadsheets in my job (oil and gas). Please excuse my very poor knowledge and dumb questions.

There is an xls we use which pulls in a lot of information from another application (PI processbooks) and as a result is running really slowly. I am trying to write a code which will find a row by date (looking for a date that is always yesterday) then copy and paste values everything prior to that. I am really struggling! I am trying to do the following

Find and select a cell in column A that matches another cell (J1). J1 is yesterdays date now()-1. Copy and paste values from that matched cell columns C:H up to the start of the workbook.

However I can't even get the find and select bit right, after hours of googling yesterday. A1 = B1, with the same date format as J1, though i am not sure if this is necessary?

Current code is

Dim Found As Range
Range("A1").Select
Set Found = Cells.find(What:=Sheets("Sheet1").Range("J1").Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)

Thanks in advance for any help and again apologies for basic errors and silly questions.

 

[Sektor]

Sub FindDate()
    Dim rngSearch As Range '//Where to search
    Dim rngFound As Range  '//Range with found date
    Dim dtSearch As Date   '//What search
    
    dtSearch = #2/19/2022# '//Define date to search
    Set rngSearch = Columns("A") '//Use column A for search
    Set rngFound = rngSearch.Find(What:=dtSearch, SearchOrder:=xlPrevious) '//Do searching
    If Not rngFound Is Nothing Then
        '// Found
        With rngFound
            '// Copy to the first row
            Cells("C1:H1").Value = Range(Cells(.Row, "C"), .Cells(.Row, "H")).Value
        End With
    Else
        '// Did not find. Do something about it...
    End If
End Sub

 

[mohadin]

May be

Sub test()
    Dim Found As Range
    Set Found = Cells.Find(What:=Sheets("Sheet1").Range("J1").Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
                           xlPart, SearchDirection:=xlDown)
    Cells(1, 3).Resize(Found.Row, 5).Copy Cells(1, 16)
End Sub

 

[VBAdonkey]

Thanks for the replies folks, neither of those seem to work though. I don't get any errors, just nothing happens? In the example picture in OP, I run it and the selected cell would still be A16.

 

[mohadin]

Sub test()
    Dim Found As Range
    Dim x
    Set Found = Cells.Find(What:=Sheets("Sheet1").Range("J1").Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
                           xlPart, SearchDirection:=xlDown)
    x = Found.Row
    Sheets("Sheet1").Cells(1, 3).Resize(x, 5).Copy Cells(1, 16)
End Sub

 

[VBAdonkey]

Sub test()
    Dim Found As Range
    Dim x
    Set Found = Cells.Find(What:=Sheets("Sheet1").Range("J1").Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
                           xlPart, SearchDirection:=xlDown)
    x = Found.Row
    Sheets("Sheet1").Cells(1, 3).Resize(x, 5).Copy Cells(1, 16)
End Sub

I get a run time error '91' object variable or With block variable not set, x = found.row highlighted.

 

[Marc L]

Hello,​

​

this error occurs when there is nothing found so you must check before the Found variable like you can see in the VBA example of Range.Find …​

​

You can also use the MATCH worksheet function rather than Range.Find.​

​

For further help if really necessary as my Excel version can't work from a picture the better is you link your workbook on a files host website like Dropbox …​

 

[VBAdonkey]

Hello,​

​

this error occurs when there is nothing found so you must check before the Found variable like you can see in the VBA example of Range.Find …​

​

You can also use the MATCH worksheet function rather than Range.Find.​

​

For further help if really necessary as my Excel version can't work from a picture the better is you link your workbook on a files host website like Dropbox …​

Thanks for the advice.