2

I am trying to do something as simple as capturing the value of cells J1 and I2 (name of column and row, respectively) from the following table, with J2 as a starting point.

DataSource

Sub FindTotal()

Dim strAddress As String
Dim rngValues As Range
Dim AssetNme As String, DateRng As String
Dim FrstValue As Integer

'Activate Sheet1
Worksheets("Sheet1").Activate

'Request starting cell. Should be first cell of values in the matrix
strAddress = Application.InputBox("What is the first cell of values in the matrix?")
Set rngValues = Range(strAddress)
rngValues.Select

'Capture Asset and date
FrstValue = ActiveCell.Value
AssetNme = ActiveCell.Offset(-1, 0).Value
DateRng = ActiveCell.Offset(0, -1).Value

End Sub

After I run the code, input J2 in the InputBox and press Enter, the assignments fail to show in the Immediate Window.

What am I doing wrong?

ImmediateWindow

Thadeu Freitas Filho
  • 169
  • 7
  • 6
    You have to stop the code on `End Sub` to do this. When the code finishes, there is nothing there. Even better, use the line `Debug.Print FrstValue, AssetNme, DateRng` before `End Sub`. – VBasic2008 Feb 20 '22 at 00:03
  • 3
    All non-Static method-level (ie. Sub/Function) variables go out of scope as soon as your code ends, and get garbage-collected. – Tim Williams Feb 20 '22 at 03:19
  • Here's more info about [debugging VBA](http://www.cpearson.com/excel/debuggingvba.aspx). – ashleedawg Feb 20 '22 at 09:14
  • Does this answer your question? [When should an Excel VBA variable be killed or set to Nothing?](https://stackoverflow.com/questions/19038350/when-should-an-excel-vba-variable-be-killed-or-set-to-nothing) – TylerH Mar 24 '23 at 20:22

0 Answers0