Help with vba code placement please

[Get_Involved]

I have this code in my “Thusworkbook”

Sub COPY()
    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

It doesn’t work. I have tried every way I can think of, you name it, I tried to rewrite to MAX just work.
I am not knowledgeable enough with VBCODE.
Can this code be rewrite with Max instead?
The Max I want to find is in “Bid Calculator C29:G29”
I would appreciate any help with this mater, please tell me where to place it.
Thanks

 

[alansidman]

Instead of presenting code that doesn't work and asking to figure it out for you, please explain in detail what you wish to happen. Provide examples and we will help you with workable code.

 

[Get_Involved]

alansidman Thanks for the reply sorry about that.

I have 2 worksheets worksheet 1 is Bid Calculator “C29:G29” are the cells I working with I need the Max number in the range to be placed in worksheet 2 “Legend R4”

Then after saving and closing the workbook.

That max number needs to be placed in “Bid Calculator C29” when reopening, and updating the workbook.
Can't use the formula in the cell because it give a circular reference.

 

[alansidman]

Sub AMS()
    Dim rng As Range
    Dim m As Variant
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Bid Calculator")
    Set s2 = Sheets("Legend")
    Set rng = s1.Range("C29:G29")
    m = Application.WorksheetFunction.Max(rng)
    s2.Range("R4") = m
    s1.Range("C29") = m


End Sub

 

[Get_Involved]

alansidman Thank s for the reply
The code is not giving any error code.
I Have put the code in each page starting with Thisworkbook one page at a time.
then put it in all 3 pages. then made a module 2 in Thisworkbook.
when I close and save, then reopen every thing is normal.
the numbers have not updated.
What could I be doing wrong?

 

[alansidman]

This code is specific to Bid Calculator and Legend sheets as you have described your needs in post #3 . If post #3 does not reflect what you are trying to do, then you need to amend your requirements. VBA code is not generic. It is designed specifically to your needs.

To make it work as designed.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

 

[Get_Involved]

Alan it works fine now when I do it like you said, Thanks.
The last number in C29:G29 is the last load of the week, maybe 1 or 2 of the last cells in the range could be blank, or all full.
Q 1 Can that number be + 1?
Q 2 Can that be coded to run auto when I click yes on the update button when I open the workbook?

 

[alansidman]

Q 1 Can that number be + 1?

Which number in which cell? and it should be +1 the value in the cell you indicate?

The code can be run if you insert it into Worbook_Open event. You will need to only copy the code and not the Sub Name or Sub End.

 

[Get_Involved]

alansidman:

Works like a perfect charm thank you.
problem solved.

This is the way I got it to work.

Dim rng As Range
    Dim m As Variant
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Bid Calculator")
    Set s2 = Sheets("Legend")
    Set rng = s1.Range("C29:G29")
    m = Application.WorksheetFunction.Max(rng)
    s2.Range("R4") = m + 1
    s1.Range("C29") = m + 1

Thanks for your time and patience.