CODEWITHSUNDEEP
vbaexcelvariablesoffsetqualifiers
elee7311
elee7311

Reputation: 13

Invalid Qualifier VBA

I want to set a variable to a cell and be able to reference that cell later on.

So I set cell D2 as a string whose value is the word "medium". I am currently doing this and it works:

ActiveCell.Offset(0, 1).Value = Round(Application.Max(Range([D2].Offset(1, 0), [D2].Offset(2, 0))) / 1000, 2)

but instead of using [D2], since I want to run a For loop to do this multiple times in my code I want to say:

LoadCase = [D2]
LoadCaseNeg = [D2].offset(8,0)
ActiveCell.Offset(0, 1).Value = Round(Application.Max(Range(LoadCase.Offset(1, 0), LoadCaseNeg.Offset(1, 0))) / 1000, 2)

however this doesn't work and gives me an invalid qualifier error, I'm new to VBA so is there any better way to do this?

Upvotes: 1

Views: 951

Answers (1)

John Coleman
John Coleman

Reputation: 51988

Use a Range variable:

Dim LoadCase As Range
Set LoadCase = Range("D2")

You would similarly need to use Set for LoadCaseNeg (which should also be declared to be a range variable):

Set LoadCaseNeg = LoadCase.offset(8,0)

[D2] is a way to refer to the value which is contained in the cell, not the cell itself. You can also refer to that value as Range("D2").Value or LoadCase.Value (once you set the variable). The square bracket notation isn't used all that often in VBA code since it isn't very flexible.

Any basic book/tutorial on Excel VBA should explain how to work with range variables. They are the work-horse of Excel VBA programming.

Upvotes: 1

Related Questions