Help with adding vba code to existing code

[Get_Involved]

Is there a way to code "Bid Calculator (C29:G29)" the number in the last cell before G29 example [c29 is 1, D29 is 2, E29 is 3], and F,G are blank.
and add the cell E29 is 3 to this

With Sheets("Legend").Range("R4")
 .Value = .Value + 1[CODE/]
Lets say R4 gives the number 4 and E29 is number 3 total = 7
Thanks in advance!

 

[farmerscott]

Hi,

can you post the code you are using?

Is "Bid Calculator" a separate sheet or a named range?

Can I have a guess and say you are looking to add the values in Sheets("Legend").Range("R4") to Sheets("Bid Calculator").Range("E29")

thx.

 

[Get_Involved]

Thanks for the reply farmerscott:
It's the opposite.
Step 1
Sheets("Legend").Range("R4") has this code,

Private Sub Workbook_Open()
 Dim Ans As String
 Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
 If Ans = vbYes Then
 
 With Sheets("Legend").Range("Q4")
 .Value = .Value + 1
End With

Step 2

Sheets("Legend").Range("Q4:Q18").Value = CInt(Format(Date, "ww", 1))
   End Sub

This all works, then I got the brite idea to try and create a 3rd step.

Hopeful step 3

Public Sub TestMe()
Sheets("Bid calculator").Range("C2").Value = WorksheetFunction.Max(Range("Legend")"R4")) + 1
End Sub

The bid Calculator in cell "C29" pulls it's first number from the "Legend")"R4"

I have rewrote this code that works in a new Workbook.

Public Sub TestMe()
If Range("G2") >= WorksheetFunction.Max(Range("I11:I21")) Then
End If

Range("G2") = WorksheetFunction.Max(Range("I11:I21")) + 1
End Sub

I can't get it to work as my third step of 3.
Thanks in advance.

P.S.
A computer programer on the mother boards of the GEMNI space cones, said to me.
"If you can think about it, you can make a computer do it".
I don't remember his name.
He was now driving a truck, and told me this in a truck stop.
I have never forgot that day.
That is the reason I love Excel spreadsheets.

 

[Get_Involved]

Sorry took to long to edit.
step 3

Public Sub TestMe()
If Range("C2") >= WorksheetFunction.Max(Range("C29:C29")) Then
End If

Range("C2") = WorksheetFunction.Max(Range("C29:C29")) + 1
End Sub

is in worksheet "Bid Calculator"

 

[Get_Involved]

farmerscott:
In reference to your first post the answer is yes.
It actually to Sheets("Bid Calculator").Range("C29")

 

[farmerscott]

I need some more clarification, in order of your posts above-

1.

Is there a way to code "Bid Calculator (C29:G29)" the number in the last cell before G29 example [c29 is 1, D29 is 2, E29 is 3], and F,G are blank.

Are you trying to find the max value in the range, to the left of cell G29. Are the numbers in the cells from C29 going to be increasing (as per your numbers 1,2,3 above) or can the largest number be in the first cell?

Are your figures like this
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 29[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]blank[/TD]
[TD]blank[/TD]
[TD]Answer= Range("E2").value=3[/TD]
[/TR]
</tbody>[/TABLE]

or is it more like this.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 29[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]blank[/TD]
[TD]Answer= Range("D29").value=15[/TD]
[/TR]
</tbody>[/TABLE]

2. In the following code

Private Sub Workbook_Open()
 Dim Ans As String
 Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
 If Ans = vbYes Then
 
 With Sheets("Legend").Range("Q4")
 .Value = .Value + 1
End With

when someone clicks "yes" then the invoice number goes up by 1? Correct? Is the number being calculated in Range("R4") or Range("Q4")? You mentioned Range("R4") as an introduction to your code

Sheets("Legend").Range("R4") has this code,

but use Range("Q4") in your code?

3. This line of code is working OK?

Sheets("Legend").Range("Q4:Q18").Value = CInt(Format(Date, "ww", 1))

It is providing you with a number in each of the cells?

4.I am not sure why you are using the MAX function on only one cell?

Public Sub TestMe() Sheets("Bid calculator").Range("C2").Value = WorksheetFunction.Max(Range("Legend")"R4")) + 1
End Sub

5. your last piece of code does not make sense???

Public Sub TestMe()
If Range("C2") >= WorksheetFunction.Max(Range("C29:C29")) Then
End If

Range("C2") = WorksheetFunction.Max(Range("C29:C29")) + 1
End Sub

Your first line has a "equal to or greater than" statement while your 2nd line has an "equal to" statement.


Note: You seem to be changing your cells and ranges that you are working with, as you respond to my questions. To best help you we need to know exactly what you want. It may be best to rewrite your original question into as many smaller steps as possible. Remember we can't see your spreadsheets and we are very literal people.

cheers,

FS

 

[Get_Involved]

Sorry not getting back sooner I am a truck driver been on the road.
Are you trying to find the max value in the range, to the left of cell G29. Are the numbers in the cells from C29 going to be increasing (as per your numbers 1,2,3 above)?
Yes Sir I am Farmerscott
Here is a link to the file
https://app.box.com/s/5cjy5bnt0a9iwotuoe3pbz1m71fdfejl