Cannot pass a variable between subs

[cwilliams96]

Hi

Can anyone please help? Really struggling as a novice on VBA to pass a variable between 2 subs...

The first sub works fine and counts the number of rows in the data and stores in variable i, which is then used in that sub routine.

The second sub that runs from a button click, I need it to use that variable i in a further process on another worksheet. This is where it fails

I have tried Option Explicit, I have tried making the initial sub Public and other things from the internet, before posting here....


This is where I set the variable i in the initial sub

Dim i As Long
'This counts the rows and stores the value in i
i = Range("A" & Rows.Count).End(xlUp).Row

This is the second sub called from a button click, this is where I have tried to pass the variable,

Sub Invoice_Calc(i As Long)

'--------------------------ADDING FORMULAS TO THE INVOICE CALC SHEET---------------------

Sheets("INVOICE CALC").Select

'This is to see if the variable was passed
MsgBox "Number of Rows = " & i, vbInformation, "ROW COUNT"


Range("L6").Formula = Replace("=SUMIFS('Apps Indicative Earnings Report'!$K$11:$K$#,'Apps Indicative Earnings Report'!$G$11:$G$#,'INVOICE CALC'!H6,'Apps Indicative Earnings Report'!$C$11:$C$#,'INVOICE CALC'!J6,'Apps Indicative Earnings Report'!$D$11:$D$#,'INVOICE CALC'!K6)", "#", i)

Can anyone help?

Thanks
Chris

 

[JonXL]

I'm understanding you saying that the first sub runs to completion and you later click a button to start the second sub.

Is that correct?

If so, you can't pass variables around like that. Once the code runs through, all variables get cleared out.

That leaves two options...

1) Declare i in your second sub and define it as the number of rows - repeat this code

Dim i As Long
'This counts the rows and stores the value in i
i = Range("A" & Rows.Count).End(xlUp).Row

2) Write i from your first sub to a cell in the workbook and reference that cell in sub 2

 

[cwilliams96]

Hi

Thanks for this.. Yes your understanding is correct. Sub 1 ends, button click when you want to start sub 2...

I will do as you suggest and count the rows again

Thanks
Chris

 

[Fimpass]

Check this:

Private Sub CommandButton1_Click()Sheets("INVOICE CALC").Select
      Dim UserInput As String
      UserInput = Sheet2.Range("D" & Rows.Count).End(xlUp).Row
      Call Test(UserInput)
End Sub




Sub Test(txtInput As String)
      MsgBox txtInput
End Sub