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"

 

[Get_Involved]

Michael M Thanks

Got a question for you

Is there some code we can put into that code that keeps the other cells in "R4: R18" from advancing +1 except when Cell "C2" of the Bid Calculator has a truck number in it?

 Private Sub Workbook_Open()
Dim Ans As String
Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
If Ans = vbYes Then
Sheets("Legend").Range("R4:R18").Value = WorksheetFunction.Max(Sheets("Bid Calculator").Range("C29:G29")) + 1
End If
End Sub

This Code works except it does all the cells in the colum at the same time weather I am using the truck or not.
I placed a new copy in the drop box, https://www.dropbox.com/s/725f6hfi84p2efi/DUMMY NAME.xlsm?dl=0

 

[Michael M]

Maybe this...don't have Excel at the moment !!

Private Sub Workbook_Open()
Dim Ans As String
Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
If Ans = vbYes Then
    If Sheets("Bid Calculator").Range("C2").Value = "" Then Exit Sub
    Sheets("Legend").Range("R4:R18").Value = WorksheetFunction.Max(Sheets("Bid Calculator").Range("C29:G29")) + 1
End If
End Sub

 

[Get_Involved]

Thanks Michael M
That didn't work, or advance the numbers.
Thanks anyway.

 

[Get_Involved]

When I place a truck number in Cell "C2" of the Bid Calculator.

How can this formula be rewrit to find the number from Cell C2 of the Bid Calculator, in the "Legend" Cells "E4:E18" then update + 1 only the cell of the same row, of "R4:R18”?

Here is the code I have

Private Sub Workbook_Open()
Dim Ans As String
Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
If Ans = vbYes Then
    If Sheets("Bid Calculator").Range("C2").Value = ("Legend""E4:E18") Then Exit Sub
    Sheets("Legend").Range("R4:R18").Value = WorksheetFunction.Max(Sheets("Bid Calculator").Range("C29:G29")) + 1
End If
End Sub

The numbers in the sheet "Legend" Row R4:R18 represent the numbers of loads each truck has hauled.

https://www.dropbox.com/s/725f6hfi84p2efi/DUMMY NAME.xlsm?dl=0

 

[Michael M]

try

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

 

[Get_Involved]

Thanks Michael M
It is giving me this message.

Compile error:
For Each Control variable must be Variant or Object

 

[Michael M]

Sorry...my mistake

Private Sub Workbook_Open()
Dim Ans As String
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 Exit Sub
    Next cell
    Sheets("Legend").Range("R4:R18").Value = WorksheetFunction.Max(Sheets("Bid Calculator").Range("C29:G29")) + 1
End If
End Sub

 

[Get_Involved]

It is working now.
The problem it is updating each row with the same number all through from e4 to e18.
can we make it just update the truck number in example "C2" 4346, find 4346 in the Legend and update R5 only, and not the others?

 

[Michael M]

Try

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 = Sheets("Legend").Cells(cell.Row, 5).Offset(, 13).Value + 1
            Exit Sub
        End If
    Next cell
    Sheets("Legend").Range("R4:R18").Value = WorksheetFunction.Max(Sheets("Bid Calculator").Range("C29:G29")) + 1
End If
End Sub

 

[Get_Involved]

Thanks Michael M
The Code is working like I want, except it is only updating "C29", not the MAX "C29:G29".
Sometimes there may be only 1 load in "C29" and the other columns will be empty, or maybe 2 or more or all of the columns, depending on how many loads will be on that trip.
Thank You