Simplifying Code Using For-Next Loop

**inwalkedbud** · 07-02-2008, 11:39 PM

Looking to simplify the following code possibly with For-Next Loop.

Sub Simplify()

With Workbooks("Book1")
     
     .Sheets("Sheet1").Activate
     Call MySub_1

     .Sheets("Sheet2").Activate
     Call MySub_2

     .Sheets("Sheet3").Activate
     Call MySub_3

     .Sheets("Sheet4").Activate
     Call MySub_4

End With

End Sub

Where the My Subs are private sub procedures. Thanks.

**mikerickson** · 07-03-2008, 12:37 AM

This question was also asked at
http://www.mrexcel.com/forum/showthread.php?t=328091
There are a couple of options there, both use Application.Run

**rylo** · 07-03-2008, 01:04 AM

Hi

Try this....

For i = 1 To 4
    Sheets("Sheet" & i).Activate
    Application.Run ThisWorkbook.Name & "!sheet" & i & ".MySub_" & i
Next i

rylo

**inwalkedbud** · 07-03-2008, 01:25 AM

thanks for the response. What if the sheets were named (ex. "A", "B", "C" etc...) instead of numbered?

**rylo** · 07-03-2008, 01:48 AM

Hi

Does the macro have the same suffix as the sheet (ie A,B...) or does it still have the number suffix?

rylo

**inwalkedbud** · 07-03-2008, 02:37 PM

sorry, but i tried to give a generic example in the first code section. code looks more like this. thanks.

Sub Simplify()

With Workbooks("MyWorkbook")
     
     .Sheets("A").Activate
     Call MySub_A

     .Sheets("B").Activate
     Call MySub_B

     .Sheets("C").Activate
     Call MySub_C

     .Sheets("D").Activate
     Call MySub_D

End With

End Sub

**inwalkedbud** · 07-12-2008, 01:01 PM

i tried to add this code to the previous post to eliminate the compile error but, i get new compile error: syntax error. any thoughts. thanks.

Dim r_ As String
Dim f_ As String
Dim form As String

r_ = "r_"
f_ = "f_"
form = ".formula"

Dim i As Integer
For i = 1 To 3
    r_ & i & form = f_ & i
Next i

End Sub

**mikerickson** · 07-12-2008, 07:03 PM

VB does not treat strings resulting from concatenation as variables.
VB does not treat strings resulting from concatenation as properties, methods, functions, collections, or objects.

The approach you have been trying throughout this thread (refering to the argument a_1 as "a_" & "1") is fatally flawed.

If you describe what you are trying to do, a successful approach (probably involving arrays) might be found.

**inwalkedbud** · 07-12-2008, 07:06 PM

mike--

is there any way to reduce the redundancy then? thanks. bud.

**mikerickson** · 07-12-2008, 10:57 PM

You should look into arrays.
The code above could be done like this:

Dim i As Long
Dim myRange(1 To 3) As Range

For i = 1 To 3
    Set myRange(i) = Cells(1, i)
Next i

Dim formulaString(1 To 3) As String

For i = 1 To 3
    formulaString(i) = "=" & CStr(2 * i - 1) & "+" & CStr(2 * i)
Next i

For i = 1 To 3
    myRange(i).FormulaR1C1 = formulaString(i)
Next i

This could be further simplified, to one loop

Dim myRange(1 To 3) As Range
Dim formulaString(1 To 3) As String
Dim i As Long

For i = 1 To 3
    Set myRange(i) = Cells(1, i)
    formulaString(i) = "=" & CStr(2 * i - 1) & "+" & CStr(2 * i)
    myRange(i).Formula = formulaString(i)
Next i

Starting with arrays and using For..Next loops makes it easier to see this version.

Dim i As Long
For i = 1 to 3
    Cells(1,i).FormulaR1C1 = "=" & CStr(2 * i - 1) & "+" & CStr(2*i)
Next i

Unless you need myRange or formulaString later in the routine, this would be the way to go.

Simplifying Code Using For-Next Loop

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Simplifying Code Using For-Next Loop

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions