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]

It appears your cells C29:G29 are "center across selection" formatted.....Why ????
Change that to General and see if that does the trick.

 

[Get_Involved]

It is still doing the MIN which is cell "C29"

 

[Michael M]

So where is the MIN coming from as it's not in the workbook_Open code ????
Are there any other codes running?

 

[Get_Involved]

Michael M

I could not find a MIN code any where.
So I Backed up the workbook and renamed it.
I then striped all codes out of the Bid Calculator, except Cells (C29:G29).
When I update the workbook it still gives me the MIN number in cell "C29" not the MAX of "C29:G29".
I even striped out the Conditional Formatting.
This has me baffled.

 

[Get_Involved]

Michael M

I reversed the sheets lines, got it to doing just what I want to do in the Bid Calculator, it finds the MAX, and puts it in "C29".
the problem now is it fills every row in "R4:R18". I just need the row of the truck number I am working from Bid Calculator "C2".

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").Range("R4:R18").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
End If
End Sub

 

[Michael M]

Thats what this one does !!!

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").Cells(cell.Row, 5).Offset(, 13).Value = Sheets("Legend").Cells(cell.Row, 5).Offset(, 13).Value
End If
End Sub

 

[Get_Involved]

Sorry about that in my excitement I posted the wrong.
Here is the on

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

 

[Get_Involved]

Sorry about that in my excitement I posted the wrong code

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").Range("R4:R18").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

Except it places the number in every row of R4:R18, I just need it to place in the row that the truck number is in, not the other ones.

 

[Michael M]

HAve you tried the code in Post #36....but make sure you have removed the excess numbers from Column "R" FIRST !!!!!

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").Cells(cell.Row, 5).Offset(, 13).Value = Sheets("Legend").Cells(cell.Row, 5).Offset(, 13).Value
End If
End Sub

 

[Get_Involved]

Thanks Michael M

This code finds the MAX number in the Bid Calculator Cells "C29:G29 and places in in Cell "C29" after updating. Works Right.
But puts the MAX number in all the Cells of the Legend "R4:R18', Not what I want on the Legend page.
Just need it to change in the row of the truck number from that is in Cell "C2" of the Bid Calculator
I need it to do what the code in #36 does.
But it needs to put MAX number, not the MIN in Cell "C29" of the Bid Calculator.

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").Range("R4:R18").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

********
This is the Code that is in # 36
It works right in the Legend Cells R4:R18, but it places the MIN not the MAX in Cell "C29" of the Bid Calculator.

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").Cells(cell.row, 5).Offset(, 13).Value = Sheets("Legend").Cells(cell.row, 5).Offset(, 13).Value
End If
End Sub

Hope I explained it right.
By the way I drive a truck, that is the reason it takes me so long to answer you back.