excel VBA - for loop for several sheets

[paku]

Hi

I want to do loops on these line of codes for Sheet1 and Sheet2.
Is there a way I could make the Sheet number as a variable
e.g. Sheet(x).cells(Counter, 1)?

Private Sub GCDButton_Click()


For Counter = 1 To CipherTextBox.TextLength
Sheet1.Cells(Counter, 1) = Counter
Sheet1.Cells(Counter, 2) = Mid(CipherTextBox.Text, Counter
3)
Sheet2.Cells(Counter, 1) = Counter
Sheet2.Cells(Counter, 2) = Mid(CipherTextBox.Text, Counter
4)
Next

Columns("A:B").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending
Key2:=Range("A1") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
DataOption2 _
:=xlSortNormal

'Find distance
For I = 1 To CipherTextBox.TextLength
If Sheet1.Cells(I, 2) = Sheet1.Cells(I + 1, 2) Then
Sheet1.Cells(I, 3) = Sheet1.Cells(I, 1) - Sheet1.Cells(I
1, 1)
Else
Sheet1.Cells(I, 3) = ""

End If

Next


'Find Greatest Common Divisor (GCD)
Worksheets("Sheet1").Activate
Range("D1").Select
ActiveCell.FormulaR1C1 = "=GCD(RC[-1]:R["
LTrim(Str(CipherTextBox.TextLength)) + "]C[-1])"
Range("D2").Select

Thanks in advance

Did

 

[Frank Kabel]

Hi
try something like
sub foo()
Dim i
for i= 1 to sheets.count
msgbox sheets(i).name
next
end sub

 

[Bob Phillips]

Not all sheet types support cells property, so this is better as

sub foo()
Dim i
for i= 1 to worksheets.count
msgbox worksheets(i).name
next
end sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi
try something like
sub foo()
Dim i
for i= 1 to sheets.count
msgbox sheets(i).name
next
end sub


--
Regards
Frank Kabel
Frankfurt, Germany

Hi

I want to do loops on these line of codes for Sheet1 and Sheet2.
Is there a way I could make the Sheet number as a variable
e.g. Sheet(x).cells(Counter, 1)?

Private Sub GCDButton_Click()


For Counter = 1 To CipherTextBox.TextLength
Sheet1.Cells(Counter, 1) = Counter
Sheet1.Cells(Counter, 2) = Mid(CipherTextBox.Text, Counter,
3)
Sheet2.Cells(Counter, 1) = Counter
Sheet2.Cells(Counter, 2) = Mid(CipherTextBox.Text, Counter,
4)
Next

Columns("A:B").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending,
Key2:=Range("A1") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal

'Find distance
For I = 1 To CipherTextBox.TextLength
If Sheet1.Cells(I, 2) = Sheet1.Cells(I + 1, 2) Then
Sheet1.Cells(I, 3) = Sheet1.Cells(I, 1) - Sheet1.Cells(I +
1, 1)
Else
Sheet1.Cells(I, 3) = ""

End If

Next


'Find Greatest Common Divisor (GCD)
Worksheets("Sheet1").Activate
Range("D1").Select
ActiveCell.FormulaR1C1 = "=GCD(RC[-1]:R[" +
LTrim(Str(CipherTextBox.TextLength)) + "]C[-1])"
Range("D2").Select

Thanks in advance

Dido