Hi

I need your help please. My code is the following:

1 For counter = 1 To 18
2 Worksheets("Calculations").Cells(5 + counter, 27).Value = Worksheets("Data").Range("G22")
3 Worksheets("Calculations").Cells(5 + counter, 28).Value = Worksheets("Data").Range("E22")
4 Worksheets("Calculations").Cells(5 + counter, 29).Formula = "=Worksheets("Calculations").Cells(5 + counter, 28) * Worksheets("Calculations").Cells(5 + counter, 27) / 1000"
5 Next counter


I am getting some values from some specified celles in sheet "Data" ( cells G22, E22) and puting them in "Calculations" sheet in [.Cells(5 + counter, 27)] and [.Cells(5 + counter, 28)], where I do calculations in [.Cells(5 + counter, 29)]. But I get an error for the line 4. I want to have a Formula displayed in [.Cells(5 + counter, 29)] but not just a calculated number.

How can I do that. Line 4 should be similar to [Worksheets("Sheet1").Range("A1").Formula = "=$A$4+$A$10"] that has a specified cell range, but my code has a variable cell range.

Where is the mistake?

Thanks;

Hi,
I see two issues in your formula.

First is the sheet name reference. You must use the same format as what you would normally find in a spreadsheet i.e. 'Calculations'! not Worksheets("Calculations") The formula bar is still treated as a string of text

Second, for this I suggest using VBA/Excels R1C1 referencing method

For counter = 6 To 23
    Worksheets("Calculations").Cells(counter, 27).Value = Worksheets("Data").Range("G22")
    Worksheets("Calculations").Cells(counter, 28).Value = Worksheets("Data").Range("E22")
    Worksheets("Calculations").Cells(counter, 29).Formula = "='Calculations'!R" & counter _
        & "C28 * 'Calculations'!R" & counter & "C27/1000"

Next counter

I also noticed that you had the counter start at 1 to 18 but in each line you always added 5 to it. You can just start the counter at 6 and go to 23 for the same results


Hope this helps,

DaveG

DaveJoyG thanks a lot. It works absolutely fine.

I spent 2 hrs to solve it but couldn't. With your code it is much better now.

Thanks;