PDA

View Full Version : I need to reference a variance row from macro1 in macro2


NoraNora
Dec 20, 2011, 10:20 AM
My MACRO is too large so I need to break it out into smaller parts. I'm looping to pull values from 1 worksheet to another for a specified range, all value are posted along 1 row and them move to the next row for the next # in the specified loop range. The problem I'm having is maintaining this variable row in MACRO2 (and additional macros as well).

MACRO1:
Dim OutputRow As Integer
OutputRow = 1
Do Until xxxxxxxxxxx
Sheets("xxxx").Range("xxxx").Cells(OutputRow).Value = Worksheets("xxxx").Range("xxxx").Value
call to MACRO2
OutputRow = OutputRow 1
Loop

MACRO2:
Sheets("xxxx").Range("xxxx").Cells(OutputRow).Value = Worksheets("xxxx").Range("xxxx").Value

JBeaucaire
Dec 21, 2011, 03:36 AM
1) you can declare your variables as PUBLIC at the top of the module, and the values in those macros will persist and be available to all macros that are running.


Option Explicit
Public OutputRow As Long


Sub MainMacro()

For OutputRow = 1 To 1000
Sheets("xxxx").Range("xxxx").Cells(OutputRow).Value = Worksheets("xxxx").Range("xxxx").Value
Call Macro2
Next OutputRow

End Sub


Sub Macro2()
Sheets("xxxx").Range("xxxx").Cells(OutputRow).Value = Worksheets("xxxx").Range("xxxx").Value
End Sub


2) You can "feed" the secondary macros a parameter:

Sub MainMacro()
Dim OutPutRow As Long

For OutPutRow = 1 To 1000
Sheets("xxxx").Range("xxxx").Cells(OutPutRow).Value = Worksheets("xxxx").Range("xxxx").Value
Call Macro2(OutPutRow)
Next OutPutRow

End Sub


Sub Macro2(OutPutRow As Long)
Sheets("xxxx").Range("xxxx").Cells(OutPutRow).Value = Worksheets("xxxx").Range("xxxx").Value
End Sub

JBeaucaire
Dec 21, 2011, 03:38 AM
If you want to share your entire macro, we can look at it together and determine the pros/cons of using the subroutines. It may not be necessary, and if there is a lot of redundant activity I can offer some more looping ideas.