If Stmt; VBA Code; Reference to Cell Value

[Dennis]

Excel 2003

Need VBA syntax/Code to:

1)obtain a CellValue

2) If CellValue <> 0
Print Routine (I have got this OK)
Endif

Note: As new rows are entered into the Worksheet
the the cell containing the SubTotal value that
I wish to use as CellValue - moves on the W/S.


But how to represent that in VBA?

Example: "$D$32" or "D32" or ??

TIA Dennis

 

[Chip Pearson]

Dennis,

It depends on what cell you want to read. If the cell is fixed,
use code like

Dim V As Variant
V = Range("A1").Value
If V <> 0 Then
' your code here
End If

If the cell's location varies, you can try something like

Dim R As Range
Dim V As Variant
Set R = ActiveSheet.Cells(Rows.Count, "A").End(xlUp)
V = R.Value
If V <> 0 Then
' your code here
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Dennis]

Hello Chip,

The subject cell will move on the worksheet as new data
(rows) are added.

Will the second example automatically "adjust" as the data
is added?

If not, do I have to change the Cell reference in the VBA
code as that cell moves?

Is "variant" similar to a kind of "Offset()" function?

Dennis

 

[Chip Pearson]

Dennis,

The second example in my post will find the last used cell in
column A, and assign that cell to the variable R. It will change
as rows are added to the worksheet. Change the "A" to the
appropriate column.

Is "variant" similar to a kind of "Offset()" function?

No. A Variant type variable can contain any type of data,
including text and numeric values.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com