Cannot vlookup range after name change in VBA

问题:

Apologies if this has been asked a different way but I couldn't find this issue anywhere...

I am creating a model to analyse test data and need to vary the size of my named ranges regularly, to account for the different number of learners on each course. I have used the following code to do this:

For i = 2 To 20

With ActiveWorkbook.Names("Question" & i)
        .RefersTo = "Pivot Data 1 - To Use!$A$" & QuestionStart & ":$F$" & (LearnerNumbers + QuestionStart)
        .Visible = True
End With

QuestionStart = QuestionStart + LearnerNumbers + 1

Next i

It successfully changes the size of my ranges (yay!) but when I use vlookup(a4, Question2, 3, FALSE) in my worksheet, excel returns the #VALUE! error.

On investigation with the Name Manager I found that the "Value" property is set to the same as the "Refers To" i.e. "Pivot Data 1 - To Use!$A$22:$F$37" rather than listing the contents of the range as expected.

I'm not sure what I have omitted from my code but any help will be much appreciated.

Thanks, Steph

回答1:

• First of all even in the RefersTo string the = sign is required as that is what it RefersTo. When you go the the name manager check what you have entered there, it should start with an = sign.
• Secondly, there is a space in the sheet name you will need to explicitly enclose the sheet name in single quotes.

So you can modify your code to

.RefersTo = "='Pivot Data 1 - To Use'!$A$" & QuestionStart & ":$F$" & (LearnerNumbers + QuestionStart)

回答2:

You are missing the = sign on the front so Excel thinks you have defined a Name not as a formula referring to a range but as a piece of text