Help rewriting VB Code:

[Get_Involved]

How do I get this code to work?

Private Sub Workbook_Open()
 Dim Ans As String
 Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
 If Ans = vbYes Then
 
 With Sheets("Expense Report").Range("H4")
 .Value = .Value + 1
 
 With Sheets("Legend").Range("Q4")
 .Value = .Value + 1
 
 With Sheets("Legend").Range("R4")
 .Value = . Value + MAX('Bid Calculator'!C29:G29)

Range R4 is the one I am trying to get to work.

I have the code in "ThisWorkbook"

 

[Michael M]

Okay tr this....
But what if there is no matching truck in Legends E4:E18

Private Sub Workbook_Open()
Dim Ans As String, cell As Range
Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
If Ans = vbYes Then
    For Each cell In Sheets("Legend").Range("E4:E18")
        If cell.Value = Sheets("Bid Calculator").Range("C2").Value Then
            Sheets("Legend").Cells(cell.Row, 5).Offset(, 13).Value = WorksheetFunction.Max(Sheets("Bid Calculator").Range("C29:G29")) + 1
            Exit Sub
        End If
    Next cell
   ' Sheets("Legend").Cells(cell.Row, 5).Offset(, 13).Value = Sheets("Legend").Cells(cell.Row, 5).Offset(, 13).Value + 1
End If
End Sub

 

[Get_Involved]

Thank You
Michael M
Thanks

Works like a charm.
If there is no matching trucks in Legend E4:E18, that means I fat fingered a key in Cell C2 of the Bid Calculator
Will that create a problem?

Thank You for not giving up on me
And most of all thanks for not letting me give up.
Problem Solved

Would you explain what the 5 and , 13 stands for in the Legend line of code.

 

[Michael M]

This

Cells(cell.Row, 5).Offset(, 13).Value


breaks down to

Cells(the selected row in the loop, column 5 (which is column "E")).Offset(the same row, 13 columns to the right ( WHich is "R")).Value

Private Sub Workbook_Open()
Dim Ans As String, cell As Range
Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
If Ans = vbYes Then
    For Each cell In Sheets("Legend").Range("E4:E18")
        If cell.Value = Sheets("Bid Calculator").Range("C2").Value Then
            Sheets("Legend").Cells(cell.Row, 5).Offset(, 13).Value = WorksheetFunction.Max(Sheets("Bid Calculator").Range("C29:G29")) + 1
            Exit Sub
        End If
    Next cell
End If
End Sub

 

[Get_Involved]

Thank you for all your help

 

[Michael M]

Glad we worked it out...