excel VBA - for loop for several sheets
 

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

--
Message posted from http://www.ExcelForum.com

excel VBA - for loop for several sheets
 

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


---
Message posted from http://www.ExcelForum.com/

excel VBA - for loop for several sheets
 

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)

"Frank Kabel" wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/