My Range.Find only finding the first Argument

Here is my code it should find the only cell in Sheet 3 that contains the words ie: "Total Joe Painting"

After it copy the cell 2 to the right of it. However that is not the problem right now when I change the value of Sheet1.Cells(2,2) it does not correctly go to that field instead it still only finds the first cell with Total.

Sub SearchRetainage()

    Worksheets("Sheet3").Activate
    Cells.Find(What:="Total " & Sheet1.Cells(2, 2).Text, After:=Sheet3.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    
End Sub

A button, is that what your asking?

A user inputs his data into the field 2,2 and then presses button that triggers macro. Then he should be able to enter another name and it find the other name and represent the coresponing number. However it still references the first one.

After changing the above problems to correspond the sheets correctly I now get an error code: Object variable or With block variable not set

Sub SearchRetainage()

    Worksheets("Sheet1").Activate
    Cells.Find(What:="Total " & Sheet1.Cells(2, 2).Text, After:=Sheet3.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    
    
End Sub

Never mind works perfect! thank you I followed what you said and addressed the method problem. Works flawlessly now!

Yeah I am doing some research to handle such

    Worksheets("Sheet3").Activate
    Cells.Find(What:="Total " & Sheet1.Cells(2, 2).Text, After:=Sheet3.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 20).Range("A1").Select
    Selection.Copy
    Worksheets("Sheet1").Activate
    Sheet1.Cells(6, 2).Activate
    ActiveCell.PasteSpecial (xlPasteValues)

Is there a value If Find returns an error than I can input a 0 into that field? Since there is no value to begin with I just want it to return 0. Sounds like a If then statement.

When I look at my Project Explorer I think I found the problem you are speaking of.

I see:

Microsoft Excel Objects
..Sheet1(Sheet3)
..Sheet2(Sheet1)
..Sheet3(Sheet2)
..ThisWorkbook

I would assume this is the case for my problems, do I change them back to Sheet1(sheet1) and so on and so forth?

I am not sure what you are speaking of when you say different methods to reference.

Originally Posted by Norie

If you want to check if Find has worked you can try something like this.

Dim rngFnd As Range

   Set rngFnd =  Worksheets("Sheet3").Cells.Find(What:="Total " & Sheet1.Cells(2, 2).Text, After:=Sheet3.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

   If rngFnd Is Nothing Then
          MsgBox "Not found."
          Exit Sub
   Else
           rngFnd.Offset(0, 20).Copy
           Sheet1.Cells(6, 2).PasteSpecial xlPasteValues
   End If

Used a modified version of this to loop and then loop to check if it has the receiving amounts. Works GREAT! Norie thank you so much for the loop help! And JS for getting me off to the right track.

I am still learning the mapping system but is it bad to say that this VBA code is quite enjoyable! Spend a few hours creating a vba program and I save myself 15 hrs a month

Can we add solved tags?