Variable is always zero

Question

I'm trying to assign a variable to a cell value but I'm always getting zero for some reason.

Sheets(3).Activate

RowNo = 30
ColNo = 2

EU1 = Range(Cells(RowNo, ColNo).Address()).Value

MsgBox EU1

Check the image for cell address and value

The reason why I'm using Range(Cells(RowNo, ColNo)) is because I have a table that I need to loop into to check the date, if it equals toady (or less than today) the variable EU1 will be updated.

You could just use `EU1 = Cells(RowNo, ColNo).Value`. `Sheets(3)` may not be the sheet you think it is? — SJR, Jan 21 '19 at 18:56
Qualify your call to `Range` with a worksheet reference. See [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Comintern, Jan 21 '19 at 18:56
Definitely do not use `.Activate` or `.Select` those are actions of users, not code. Just reference the sheet you want for the range/cell `EU=Sheets(3).Cells(RowNo, ColNo).Value` That's very specific then and you don't have to guess when you look at this line at which worksheet it's referring. — JNevill, Jan 21 '19 at 19:03
Does `ActiveSheet.Name` yield the sheet name you're expecting? — Mathieu Guindon, Jan 21 '19 at 19:39

VBasic2008 · Answer 1 · 2019-01-21T19:46:18.833

Return Value

A Few Tips

If you have values that will not change, use constants (Const).
Declare other variables with Dim.
At the beginning of each module, always use Option Explicit so VBA will alert you if variables aren't declared (Dim, or Const ... for now).
For objects (Worksheets, workbooks, ranges) you have to use the Set statement.

The Code

Option Explicit

Sub ReturnValue()

    Const cSheet As String = "Sheet3"
    Const cRow As Long = 30
    Const cCol As Long = 2

    Dim ws As Worksheet
    Dim EU1 As Double

    Set ws = ThisWorkbook.Worksheets(cSheet)

    EU1 = ws.Cells(cRow, cCol).Value

    MsgBox EU1

End Sub

Variable is always zero

1 Answers1

Return Value

A Few Tips

The Code