Pass Info Between Macros (Re-Visited)
 

Hello, The last time I posted this question the Suggestion didn't work and I
didn't get much more help so I'm re-asking for help.

I have a Dim that us the same in multiple macros shown below

Dim RowID
RowID = ActiveCell.Row


How do I use the same RowID in the Original Sub and when it's done running
it passes the same value of RowID to the next sub?

Originating Sub - InsertSection()
2nd Sub - SetBorders()
3rd Sub - InsertFormulas()
4th Sub - AddText()
5th Sub - SetColors()
6th Sub - ConditionalFormatting()
7th Sub - SetNames()

There's more but you get the point. All of them start off with the very same
Dim shown above and they all end with the same Range Selection

Range("A" & RowID).Select

Any help would be awesome.

Thanks in Advance.
Rob

Pass Info Between Macros (Re-Visited)
 

First you intimated that all the subs would use the one rowID variable. So
the responder suggested using a public variable.

You responded
I didn't know you could do that?? What about the Subs that need to use a
different value for RowID?

So why ask the same question. For passing the value, you got a good anwer.
At the top of the module do

Public RowId as Long

outside any procedure.

Then in any procedure that needs to use this common value of rowID or needs
to update a new value for the common rowID variable, do not declare a local
version of rowID.

In any sub that you want to determine its own rowID and will not need to
pass to any other subs, then declare a local variable

Sub NeedsUniqueRowID()
Dim rowID as Long


End Sub

Also, in any of the procedures that don't need the common rowID, it might be
easier to use a different variable name.


Hope that clears it up for you. Look in Excel VBA help at SCOPE of
variables.

--
Regards,
Tom Ogilvy



"Rob" wrote in message
...
Hello, The last time I posted this question the Suggestion didn't work and
I
didn't get much more help so I'm re-asking for help.

I have a Dim that us the same in multiple macros shown below

Dim RowID
RowID = ActiveCell.Row


How do I use the same RowID in the Original Sub and when it's done running
it passes the same value of RowID to the next sub?

Originating Sub - InsertSection()
2nd Sub - SetBorders()
3rd Sub - InsertFormulas()
4th Sub - AddText()
5th Sub - SetColors()
6th Sub - ConditionalFormatting()
7th Sub - SetNames()

There's more but you get the point. All of them start off with the very
same
Dim shown above and they all end with the same Range Selection

Range("A" & RowID).Select

Any help would be awesome.

Thanks in Advance.
Rob

Pass Info Between Macros (Re-Visited)
 

Rob wrote:
Hello, The last time I posted this question the Suggestion didn't work and I
didn't get much more help so I'm re-asking for help.

I have a Dim that us the same in multiple macros shown below

Dim RowID
RowID = ActiveCell.Row


How do I use the same RowID in the Original Sub and when it's done running
it passes the same value of RowID to the next sub?

Originating Sub - InsertSection()
2nd Sub - SetBorders()
3rd Sub - InsertFormulas()
4th Sub - AddText()
5th Sub - SetColors()
6th Sub - ConditionalFormatting()
7th Sub - SetNames()

There's more but you get the point. All of them start off with the very same
Dim shown above and they all end with the same Range Selection

Range("A" & RowID).Select

Any help would be awesome.

Thanks in Advance.
Rob

Pass Info Between Macros (Re-Visited)
 

Rob wrote:
Hello, The last time I posted this question the Suggestion didn't work and I
didn't get much more help so I'm re-asking for help.

I have a Dim that us the same in multiple macros shown below

Dim RowID
RowID = ActiveCell.Row


How do I use the same RowID in the Original Sub and when it's done running
it passes the same value of RowID to the next sub?

Originating Sub - InsertSection()
2nd Sub - SetBorders()
3rd Sub - InsertFormulas()
4th Sub - AddText()
5th Sub - SetColors()
6th Sub - ConditionalFormatting()
7th Sub - SetNames()

There's more but you get the point. All of them start off with the very same
Dim shown above and they all end with the same Range Selection

Range("A" & RowID).Select

Any help would be awesome.

Thanks in Advance.
Rob

Hi Rob,

It appears that you are wanting to use a variable to handle the RowID
which will be based on the active cell and will not be constant. If
this is the case, then you could assign the row number to the variable
in the first procedure and pass that value along to the next procedure.
I apologize in advance if I have misunderstood your question but
here's what I would do . . .

Sub getRowNumber
dim myRow as long
myRow = ActiveCell.Row
'now we can send this value along to the next procedure
'by using the variable as an argument in the calling procedure
Call nextProcedure(myRow)
End Sub

Sub nextProcedure(myRow)
MsgBox myRow 'just to visually see the row number for validation
Range("A",myRow).Select
End Sub

Does this help or have I misunderstood the question?

Pass Info Between Macros (Re-Visited)
 

MrScience,
Sorry for the long wait for a responce from your post, I have been very
busy. Yes you understood my plight very well and your answer is exactly what
I was hoping for. And it works perfectly too!! I kept getting dod gamn errors
so I just gave up, I am so glad I finally saw your responce.

Thanks Sooo Much!
Rob

"MrScience" wrote:


Rob wrote:
Hello, The last time I posted this question the Suggestion didn't work and I
didn't get much more help so I'm re-asking for help.

I have a Dim that us the same in multiple macros shown below

Dim RowID
RowID = ActiveCell.Row


How do I use the same RowID in the Original Sub and when it's done running
it passes the same value of RowID to the next sub?

Originating Sub - InsertSection()
2nd Sub - SetBorders()
3rd Sub - InsertFormulas()
4th Sub - AddText()
5th Sub - SetColors()
6th Sub - ConditionalFormatting()
7th Sub - SetNames()

There's more but you get the point. All of them start off with the very same
Dim shown above and they all end with the same Range Selection

Range("A" & RowID).Select

Any help would be awesome.

Thanks in Advance.
Rob

Hi Rob,

It appears that you are wanting to use a variable to handle the RowID
which will be based on the active cell and will not be constant. If
this is the case, then you could assign the row number to the variable
in the first procedure and pass that value along to the next procedure.
I apologize in advance if I have misunderstood your question but
here's what I would do . . .

Sub getRowNumber
dim myRow as long
myRow = ActiveCell.Row
'now we can send this value along to the next procedure
'by using the variable as an argument in the calling procedure
Call nextProcedure(myRow)
End Sub

Sub nextProcedure(myRow)
MsgBox myRow 'just to visually see the row number for validation
Range("A",myRow).Select
End Sub

Does this help or have I misunderstood the question?