Posting to an adjacent cell

Subbie

New Member
Joined
May 11, 2019
Messages
23
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,446
Can any 2 or more weeks contain duplicate cheque numbers?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,446
Try:
Code:
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

New Member
Joined
May 11, 2019
Messages
23
Wow! Thanks mumps that works perfectly. Really appreciate you and your fast response...
Subbie
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,439
Office Version
2010
Platform
Windows
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

New Member
Joined
May 11, 2019
Messages
23
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
 

Forum statistics

Threads
1,078,504
Messages
5,340,763
Members
399,394
Latest member
farlow

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top