Help adding formula to cell using vba

markh1182

New Member
Joined
Apr 25, 2006
Messages
48
Hi, I have the following vba to input formulas in cells. However, rather than it inputting a number for the MyRow, it just adds the wording. How can I amend this to correct?

Code:
Sub SummarySheet2()

bottom = ActiveSheet.Range("A65536").End(xlUp).Row

For R = 3 To bottom Step 1

Let MyRow = 60 + 59

    Cells(R, "F").Select

    ActiveCell.FormulaR1C1 = _
        "=IF(Detail!J MyRow = ""To Summary"",""Page 1 Summary"","""")"

Next R

End Sub

Thanks, Mark
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Mark

Are you sure you want to use variable MyRow? It's value is always 60+59 (ie doesn't vary). Anyway, try:

Code:
Sub SummarySheet2() 

bottom = ActiveSheet.Range("A65536").End(xlUp).Row 

For R = 3 To bottom Step 1 

Let MyRow = 60 + 59 

    Cells(R, "F").Select 

    ActiveCell.FormulaR1C1 = _ 
        "=IF(Detail!J" & MyRow & "= ""To Summary"",""Page 1 Summary"","""")" 

Next R 

End Sub

By the way, the Let statement is optional: you could simply use

MyRow = 60+59

Best regards

Richard
 

markh1182

New Member
Joined
Apr 25, 2006
Messages
48
after adding this and running. The formula on the spreadsheet inputs with apostrophes ' ' around the number entered for MyRow (therefore the formula does not work), and this has also come out as just 119, rather than starting at 60, then incrementing by 59 on each row.

Could you advise?

Ta, Mark
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Mark

Give this a go:

Code:
Sub SummarySheet2() 
Dim bottom as Long, R as Long, MyRow as Long

bottom = ActiveSheet.Range("A65536").End(xlUp).Row 

MyRow = 60

For R = 3 To bottom Step 1 

MyRow = MyRow + 59 

    Cells(R, "F").Formula = _ 
        "=IF(Detail!J" & MyRow & "=""To Summary"",""Page 1 Summary"","""")" 

Next R 

End Sub

Richard
 

Forum statistics

Threads
1,144,392
Messages
5,724,081
Members
422,536
Latest member
Zeeshan53

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top