CODEWITHSUNDEEP
excelfunctionoffset
Anton Papazov
Anton Papazov

Reputation: 85

I'm trying to do a dynamically range offset but i get an error

I've never used this before but i get an error when i try to do a normal offset for dynamically range through the Name Manager

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)-1)

Text in the error: "The formula you typed contains error."

I must be doing something wrong but i dont know what.

Here is my sheet:

A1 1 B1 Product1
A2 2 B2 Product2
A3 3 B3 Product3

I'm trying to make an offset so i can take the value of the second column in a variable in a macro with the VLookup() function. I'm still not getting fully in this VBA and this macros but i hope you understood what i want to do.

The macro should look something like that:

myProductValue = Applications.Worksheet.VLookup("e6", [someOffsetName], 2, 0)

So if the first column are the ID's and i type the ID number into cell e6 (for example) to get the value of the second column which refers to the ID given into the cell e6 into the variable "myProductValue"

Upvotes: 1

Views: 841

Answers (1)

Alex
Alex

Reputation: 1642

Your formula looks fine. It should prompt no error except a few conditions. I have also listed those in comment for your reference:

1) Your worksheet's name is not "Sheet1" (credit to Scott Holtzman)

2) Your Excel might have used semicolon instead of comma as argument separater (credit to XORLX)

3) Your formula is placed in column A of the same sheet (i.e. Sheet1), where some excel version will prompt for warning of self referencing (i.e. infinite loop)

4) You are actually trying to refers to a range with mentioned formula in Name manager.

More information is needed in order to resolve your issue. You could also try to explain what you want to achieve.

Upvotes: 1

Related Questions