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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,674
Messages
6,137,924
Members
450,099
Latest member
Pushbutton

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
Back
Top