Ask Experts Questions for FREE Help !
Ask
    NoraNora's Avatar
    NoraNora Posts: 1, Reputation: 1
    New Member
     
    #1

    Dec 20, 2011, 10:20 AM
    I need to reference a variance row from macro1 in macro2
    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).Valu e = Worksheets("xxxx").Range("xxxx").Value
    call to MACRO2
    OutputRow = OutputRow 1
    Loop

    MACRO2:
    Sheets("xxxx").Range("xxxx").Cells(OutputRow).Valu e = Worksheets("xxxx").Range("xxxx").Value
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #2

    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.

    Code:
    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:
    Code:
    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's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #3

    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.

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

How to calculate row and column reference in access? [ 1 Answers ]

Dear Sir, I have am facing problem in access programme development if you can do so please solve it at the earliest. I have to created 5 tables for the determination of sales individually and then merged all the tables to each other to find out the actual transaction in the whole day. Fields of...

Labor efficiency variance, variable overhead rate variance, variable overhead efficie [ 1 Answers ]

Becton Labs, Inc. produces various chemical compounds for industrial use. One compound, called Fludex, is prepared using an elaborate distilling process. The company has developed standard costs for one unit of Fludex, as follows: Standard Quantity Standard Price or Rate Standard Cost ...

How do you reference the last row # as a variable when the number of rows changes? [ 2 Answers ]

I download constantly changing financial worksheets, each with a different number of rows of information. I need to reference the last row in a specific column as a variable to use as a variable cell reference in Excel VBA, so that I can format the entire column from the last row, formatting...

GFI's in a row [ 4 Answers ]

I recently completed an outdoor freestanding patio with 4 outlets. My framer suggested that I install GFI's in all of them so I would know which one was having a problem at any given time. How do I wire them... line to line to line etc.. Or load to line, load to line etc.


View more questions Search