Create a cell Reference from vba variables

Question

Is it possible to select a cell if the cell definition is contained in 2 vba variables?

For example if you have a variables L and j.

  : returnValue : "L" : Variant/String (From Locals Window VBE)

 and

  : j                     : 2   : Double          (From Locals window VBE)

How would you define a cell reference to L2

I have tried several variations of Range(returnvalue & j) and continue to get various runtime errors based on the different formats.

Some of the context I have been using:

test = Sheets("WirelessUnitMoBillTable  ").Range(returnValue & j).Value + ItemDescriptionValue

    Sheets("WirelessUnitMoBillTable  ").Range(returnValue & j).Select

I hope someone can give me a suggestion on how to proceed.Thank you in advance.

Answer 1

The sheet name is probably not spelled exactly right. Check it very carefully.

Answer 2

The code

Dim returnValue As String

Dim j As Double

Dim test

returnValue = "L"

j = 2

test = Sheets("WirelessUnitMoBillTable ").Range(returnValue & j).Value

should work if you really have a sheet named "WirelessUnitMoBillTable " (without the quotes, but with a space at the end of the name).

Answer 3

HansV,

You are correct- I did have a spelling problem with sheet name. You solved the original question and my second comment was my fault sorry to bother you.

Greg

Answer 4

HansV,

Thank you

I tried the following as you suggested and now get a runtime error 9 (subscript out of range).

test = Sheets("WirelessUnitMoBillTable ").Range(returnValue & j).Value

I am running this code in the VBE and stepping through the code. Looking at the Locals Window.  When the above statement executes I have the same values for

returnValue = "L" 

and

j= 2

Any additional thoughts?

Greg