Search code examples
excelvbasubscript

VBA: Setting a variable to a specific cell value

In Sheet1 of my workbook, cell B2 has the value 29.

I want to assign a cell value to a variable, num_g.

I've declared num_g as Integer and am trying to assign its value with the code:

num_g = Worksheets("Sheet1!").Cells(1, 2).Value

I'm using Cells() as I plan to replace the row with a counter.

However, I keep getting the "Subscript out of range" error (run-time error 9), and don't understand why.

How can I fix this? Declaring num_g as Long or String doesn't help, nor does formatting cell B2 as a number.

Thanks in advance.

Solution

  • Try removing the "!" in the worksheet name:

    num_g = Worksheets("Sheet1").Cells(1, 2).Value