Using a variable to select multiple sheets in Excel

[ChrisDChow]

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

[KenMacksey]

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