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]

Was it possible that the MAX number was less than the value already in cell R4 ??
Don't give up !!!
I'm sure if we have sample data we can solve it.....
can you give us examples of what is in cells

Sheets("Expense Report").Range("H4")
Sheets("Legend").Range("Q4")
Sheets("Legend").Range("R4")
AND
Bid Calculator'!C29:G29)

 

[Get_Involved]

No Sir:
Each of the 5 columns is a different load the next number is greater than the on before.
Example 1,2,3,4, & 5 that formula is supposed to pick up the 5 + 1 from the Bid Calculator and place into R4 of the Legend when I update The workbook and create a new workbook for the next trip. some times there will only be one load on that trip, and could be as many five.
The trip number is in Q4 I am not worried about at this point. It is the Load number I am trying to make work.

 

[Michael M]

You didn't really answer the questions in the previous post......I need sample data to test the process !!
Your explanation in the last post tells me nothing in the way of testing / resolving the issue.
The other otion is to upload the workbook to dropbox or similar, post a link back here, and I'll take a look there !!

 

[Get_Involved]

Thanks for the reply.
Michael M

I hope I did this link correct. https://www.dropbox.com/s/8hvgoe7zby1hknj/book2.xlsm?dl=0

The workbook is a large, I just give you the two pages I am working with if it don't help let me know.

I need the max number from the Bid Calculator (C29:G29) to the Legend, then + 1 after I update the workbook.
Then from Legend back to the Bid Calculator when it is reopened after updating.
Look at message number 10. I had it working but it was giving me a MIN instead of MAX,
Also, it was giving me to many cross-references.
That is why I think it should be VBCODE
The code that is in the VB now is what I went back to because there are no cross references
Hope it makes sense.

 

[Get_Involved]

https://www.dropbox.com/s/dra9ezvcbdr8zyk/DUMMYTEST_2.xlsm?dl=0

Here is my problem

sheet 1 is named "Legend" Header is titled, in "R3" is named [Load Number].
"Legend" is the Master page of the whole workbook.
In cell "R4" the Load Number starts with let’s say 1.

sheet 2 is named "Bid Calculator".

The number "R4" of the Legend moves to "Bid Calculator" cell "E7".
From "E7" I move it "C29"
From Cell C29:G29, cell from "D29:G29" will increase + 1
exe: 1,2,3,4,5

Now from the "Bid Calculator" cell "C29:G29" to the Legend in "R4" I need the max number put into "R4"

This VB Code is working without cross references, except instead of giving me the max number in Cell "R4" in the Legend, it is giving the MIN number, plus it is multiplying X's 2 each time I update.

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

I have twisted this formula every way possible, I don't understand how to write the VB CODE, although I am trying very hard.

Can someone help Please
Thank You

 

[Michael M]

Use

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

Also, why use such a complex formula in C29:G29....In D29

=C29+1

and drag across

Also, unMerge C10:D10....and use Format>>cells>>Alignment>>horizontal>>"center across selection"

Avoid merged cells when working with VBA at ALL costs

 

[Get_Involved]

Thanks
Michael M
It works fine except when it puts the max number in cell C29 after updating it should be + 1 of the max. I can work around it, just thought it could be done in the code.
Thanks for your help.

 

[Michael M]

Sorry I don't follow your logic

except when it puts the max number in cell C29 after updating it should be + 1 of the max

Like this ???

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

Looking at the worksheet Range("R4") should simply be incremented by 1 to the next number

 

[Michael M]

Just a thought are you saying you want the result to only be +1, if there is only an entry in C29 ??
If so, try

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

 

[Get_Involved]

Thanks
Michael M
Got it to work