By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,536 Members | 2,165 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,536 IT Pros & Developers. It's quick & easy.

Using a variable to select multiple sheets in Excel

P: 1
Hi there,

I need help with Visual Basic.
I am trying to use a variable to select different sheets

Example :

I would like to have this formula in Cells A1 of my summary sheet

Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(Sheet1!R[5]C[1]=3,""Good"",""Bad"")"

Similarly, in Cell A2 for my summary sheet, I would like to have the same formula as above, however I would like to refer to Sheet2 instead of Sheet1

Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(Sheet2!R[5]C[1]=3,""Good"",""Bad"")"

How do I make the sheets that I refer to a variable, so that I can use a loop to fill in Cells A1 to Cells A300 of my summary sheet, with the same formula but refering to 300 different sheets, ie Sheet1 to Sheet300?

Can anyone help me?

Thanks
Sep 10 '06 #1
Share this Question
Share on Google+
1 Reply


P: 6
Hi there,

I need help with Visual Basic.
I am trying to use a variable to select different sheets

Example :

I would like to have this formula in Cells A1 of my summary sheet

Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(Sheet1!R[5]C[1]=3,""Good"",""Bad"")"

Similarly, in Cell A2 for my summary sheet, I would like to have the same formula as above, however I would like to refer to Sheet2 instead of Sheet1

Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(Sheet2!R[5]C[1]=3,""Good"",""Bad"")"

How do I make the sheets that I refer to a variable, so that I can use a loop to fill in Cells A1 to Cells A300 of my summary sheet, with the same formula but refering to 300 different sheets, ie Sheet1 to Sheet300?

Can anyone help me?

Thanks


Not sure exactly what you want, but this might help point you in the right direction.

Ken


For i = 1 To 3

' use the line below to write to different sheets
' Sheets(i).Range("A" & i).Select

' will write all formulas on sheet 1
Range("A" & i).Select
FormulaToWrite = "=IF(Sheet" & i & "!R[5]C[1]=3,""Good"",""Bad"")"
ActiveCell.FormulaR1C1 = FormulaToWrite

Next i
Sep 11 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.