Posting to an adjacent cell

[Subbie]

as an old grey head approaching 70, I have decided to learn VBA. I have been grateful to this forum particularly in that Journey. Please forgive any error in protocols or procedures.
I have a Workbook which has a BankRec Sheet which has cheque numbers in AA:DY with headings (Week1 etc) in Row 2 and cheque data starting from Row 3 to 30 with column in between each week (Heading REC).
My task is to find the cheque number in Week column and then post 'Yes' in the adjacent cell in the rec column.
The UserForm4 has ComboBox1 with a dropdown list with Week1, Week2 etc to Week52 (Headings in AA:DY range Row 2)
There is a second ComboBox2 with a drop down List of the cheque numbers.
The Final ComboBox3 has the dropdown with 'Yes' and 'No'.

Everything works until the search and find portion of the code. I have tried various options and get errors of Object not found or compile issues.
The current code is:

Private Sub CommandButton1_Click()
With Application
.ScreenUpdating = False
.EnableEvents = False
End With


TargetSheet = "BankRec"
Worksheets(TargetSheet).Activate

Range("AA3").Select
Do
Cells.Find(What:="ComboBox2.Value", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate


ActiveSheet.Cells(ActiveCell + 1).Value = ComboBox3.Value

Loop


With Application
.ScreenUpdating = True
.EnableEvents = True
End With


MsgBox ("Data has been added successfully")
ComboBox1.Value = ""
ComboBox2.Value = ""
ComboBox3.Value = ""
Worksheets("Master").Activate
Worksheets("Master").Cells(1, 1).Select
End Sub
God any of your good people help me as after a few hours I am unable to solve the issue.
Any help would be gratefully appreciated.

Subbie

 

[mumps]

Can any 2 or more weeks contain duplicate cheque numbers?

 

[Subbie]

Can any 2 or more weeks contain duplicate cheque numbers?

Thanks for speedy response.

No all cheque numbers will be unique throughout the range.

 

[mumps]

Try:

Private Sub CommandButton1_Click()
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Dim num As Range
    Set num = Sheets("BankRec").Cells.Find(ComboBox2.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not num Is Nothing Then
        num.Offset(0, 1) = ComboBox3.Value
    Else
        MsgBox ("Cheque number not found.")
    End If
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    MsgBox ("Data has been added successfully.")
    ComboBox1.Value = ""
    ComboBox2.Value = ""
    ComboBox3.Value = ""
    With Sheets("Master")
        .Activate
        .Cells(1, 1).Select
    End With
End Sub

 

[Subbie]

Wow! Thanks mumps that works perfectly. Really appreciate you and your fast response...
Subbie

 

[mumps]

You are very welcome.

 

[Rick Rothstein]

Cells.Find(What:="ComboBox2.Value", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate

You have your solution from 'mumps', but I thought you would like to know why your code failed. It is because of the quote marks you placed around "ComboBox2.Value". Anything you place within quote marks becomes nothing more than a collection of characters with no meaning attached to them to the VBA interpreter. So, as written, your code line was looking for the text "ComboBox2.Value" and not for the text selected within ComboBox2 itself. Remove the quote marks the Find method would have searched for the text you intended it to search for.

 

[Subbie]

You have your solution from 'mumps', but I thought you would like to know why your code failed. It is because of the quote marks you placed around "ComboBox2.Value". Anything you place within quote marks becomes nothing more than a collection of characters with no meaning attached to them to the VBA interpreter. So, as written, your code line was looking for the text "ComboBox2.Value" and not for the text selected within ComboBox2 itself. Remove the quote marks the Find method would have searched for the text you intended it to search for.

Thanks Rick
Great to have people who help and guide. both you and mumps have been excellent.
Subbie