Passing Variables Between Procedures

[Guest]

I have a work book with six sheets
Sheet1, Sheet2. Sheet3
Sheet1UL, Sheet2UL. Sheet3UL

The flowing code works as it should for the first sheet in the array passed
to the second procedure “ClearDestinationâ€. When it jumps back up to the
first procedure “ArrayLoopâ€, sheetVar(i) holds the next sheet in the array
correctly but when it moves back to “ClearDestinationâ€, (sheetVar(i) & "UL")
still holds Sheet1UL. For the life of me I cannot figure out why it dose not
change to Sheet2UL.

ARRRRRR!

Appreciatively,
Arturo


Public NumCopies As Long
Public sheetVar As Variant

Sub ArrayLoop()
sheetVar = Array("Sheet1", "Sheet2", "Sheet3")
For i = LBound(sheetVar) To UBound(sheetVar)
Set sh = Worksheets(sheetVar(i))
Sheets(sheetVar(i)).Select
ClearDestination
Next
End Sub

Sub ClearDestination()
Dim myRange2 As Range

Set myRange2 = Sheets((sheetVar(i) & "UL")).Range("A2:N65000")
myRange2.ClearContents
End Sub

 

[Guest]

The flowing code works as it should for the first sheet in the array passed
to the second procedure “ClearDestinationâ€. When it jumps back up to the
first procedure “ArrayLoopâ€, sheetVar(i) holds the next sheet in the array
correctly but when it moves back to “ClearDestinationâ€, (sheetVar(i) & "UL")
still holds Sheet1UL. For the life of me I cannot figure out why it dose not
change to Sheet2UL.

It's occasions like these that convince you to go into the preferences and
require "Option Explicit" in all your modules. In the Visual Basic Editor,

Tools -> Options... -> Editor tab -> Require Variable Declaration

That will also protect you from typos in variable names.

The problem you're having has to do with what's in the variable i when you
get to the ClearDestination sub.

You might want to re-do the ClearDestination sub so that it requires an
argument or two. That would be better than throwing everything into the
global namespace.

--Shawn