Help with vba code please

[Get_Involved]

I have two worksheets named “Bid Calculator” and “Legend”.

I am looking for vbcode to take the number from the “Legend R4” and place in cell C29 of the “Bid Calculator” this is the first step.

There are 5 cells in the row “Bid Calculator C29:G29”

In the worksheet “Legend R4” I have a number let’s say “1”.

Let’s move the “1” to “Bid Calculator C29”

In cell D29 there will be “2”, cell E29 there will be “3”, cell F29 there will be “blank”, cell G29 there will be “blank”.

There will be times that some of the cells after C29 will be blank, so I need the MAX number in the row to be placed in “Legend R4”.

So the next time I open the workbook and update the numbers, “Legend R4” the update goes back to “Bid Calculator C29”.
The answer should be from the example above = 4, in the new update.

I know that will be a cell reference if I code the cell.

Hoping it can be done in vbcode.

Appreciate any help

 

[onlyadrafter]

Hello,

Not quite sure how you get the number 4 to be copied, isn't it 3? Also not sure when you want the code to run.

Sub COPY()
    Sheets("Legend").Range("R4").COPY Sheets("Bid Calculator").Range("C29")
    With Sheets("Bid Calculator")
        For MY_COUNT = 3 To 7
            If Cells(29, MY_COUNT).Value > MY_NO Then
                MY_NO = Cells(29, MY_COUNT).Value
            End If
        Next MY_COUNT
    End With
    Sheets("Legend").Range("R4").Value = MY_NO
End Sub

Is this code of any use?

 

[Get_Involved]

onlyadrafter: Thanks for the reply

That’s a good start.

Starting with the Number 1 in Legend R4 when I reopen the workbook and update it the right number 2 is in Bid Calculator C29.

Let’s say I use two more columns D29 will have number 3 in it, and E29 will have the number 4 in it.

Using starting Legend R4 as 1 plus the max number from the Bid Calculator C29:G29 would be in E29.
E29 needs to sum in the (Legend R4+E29) = 5.

Hope that makes sense.

I actually need it to change the value when reopening workbook after updating, I am using it as a template.

I have a button that creates a new workbook after updating.
Thanks

 

[onlyadrafter]

Hello,

Still don't quite follow.

Private Sub Workbook_Open()
    Sheets("Legend").Range("R4").COPY Sheets("Bid Calculator").Range("C29")
    With Sheets("Bid Calculator")
        For MY_COUNT = 3 To 7
            If Not (IsEmpty(.Cells(29, MY_COUNT).Value)) Then
            A = Sheets("Legend").Range("R4").Value
                MY_NO = .Cells(29, MY_COUNT).Value + Sheets("Legend").Range("R4").Value
            End If
        Next MY_COUNT
    End With
    Sheets("Legend").Range("R4").Value = MY_NO
End Sub

This code needs to go into the THIS WORKBOOK code window, not a standard module.

Any closer?

 

[Get_Involved]

its working, but not adding the MAX number from (Bid Calculator C29:G29) back to the number in Legend R4 before updating.
Maybe it needs to do it after updating.

 

[onlyadrafter]

Hello,

Does adding this code to the Bid Calculator code window resolve it?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C29:G29")) Is Nothing Then Exit Sub
        For MY_COUNT = 3 To 7
            If Not (IsEmpty(Cells(29, MY_COUNT).Value)) Then
                MY_NO = Cells(29, MY_COUNT).Value + Sheets("Legend").Range("R4").Value
            End If
        Next MY_COUNT
    Sheets("Legend").Range("R4").Value = MY_NO
End Sub

still not 100% clear of what you are trying to achieve though.

 

[Get_Involved]

onlyadrafter: Thanks
Don’t look like it’s going to work this way.
So how about let’s just do this, rewrite the code to get the max number from “Bid Calculator C29:G29”, and place that number in the “Legend R4”.
I think I can make it work that way also.
Much simpler hu.

 

[onlyadrafter]

Hi,

Sub COPY()
    With Sheets("Bid Calculator")
        For MY_COUNT = 3 To 7
            If Cells(29, MY_COUNT).Value > MY_NO Then
                MY_NO = Cells(29, MY_COUNT).Value
            End If
        Next MY_COUNT
    End With
    Sheets("Legend").Range("R4").Value = MY_NO
End Sub

i assume you cant have formulai n in LEGEND R4, =MAX('Bid Calculator'!C29:G29)

or try

Sub copy1()
    Sheets("Legend").Range("R4").Formula = "=MAX('Bid Calculator'!C29:G29)"
    Sheets("Legend").Range("R4").copy
    Sheets("Legend").Range("R4").PasteSpecial (xlPasteValues)
End Sub

or even

Sub copy1()
    Sheets("Legend").Range("R4").Formula = "=MAX('Bid Calculator'!C29:G29)"
End Sub

 

[Get_Involved]

when I use the =MAX('Bid Calculator'!C29:G29) Legend it give me a circular reference, that's the reason I thought VBCode would do the trick.
Looks like I am going to have to manual put the numbers in r4 with the max number in Bid Calculator, and hope I don't fat finger a number.
Thanks for your help