Link to home
Start Free TrialLog in
Avatar of W.E.B
W.E.B

asked on

Excel Formula vba

Hello,
with the help of Expert Jeff Darling, (Thanks)
attached has been working fine, (for 10-15 columns).

I needed to add more columns into it.

1- how can I copy the formula From Column "D", to the rest of the columns (Column "E" to Column "EW") - macro
2- After Column BB, The formula doesn't seem to get results.

Any help is appreciated.
thanks,

================
Prior related question: http:Q_28612633.html
Sample-EE-jeffld.xls
Avatar of Martin Liss
Martin Liss
Flag of United States of America image
Do a copy of one of the cells that has the formula and then pastespecial formulas into the new cells.
Avatar of W.E.B
W.E.B

ASKER

the formula is always +1 column
=HLOOKUP($B3,NewZoneXref,(D$2+3),FALSE)
=HLOOKUP($B3,NewZoneXref,(E$2+3),FALSE)
=HLOOKUP($B3,NewZoneXref,(f$2+3),FALSE)

And then , one row
=HLOOKUP($B3,NewZoneXref,(D$2+3),FALSE)
=HLOOKUP($B5,NewZoneXref,(D$2+3),FALSE)
=HLOOKUP($B5,NewZoneXref,(D$2+3),FALSE)
Avatar of Martin Liss
Martin Liss
Flag of United States of America image
I don't think I can help. Let me suggest that you send an EE Message to Jeff and ask if he can help with this question.
Avatar of W.E.B
W.E.B

ASKER

thanks for trying,
not sure how to send Jeff a direct message
Avatar of FarWest
FarWest
Just expand the NewZoneXref name  and add all rows to it
Avatar of FarWest
FarWest
you can go to formula-manage names and add the rest of rows
=Price!$C$4:$AB$108
Avatar of W.E.B
W.E.B

ASKER

Hi FarWest,
where do I expand the NewZoneXref name  and add all rows to it .
and the Formula =Price!$C$4:$AB$108

can you please help on the sample sheet

thanks,
Avatar of ChloesDad
ChloesDad
Flag of United Kingdom of Great Britain and Northern Ireland image
You problem is the range NewZoneXRef on the Price table. It only covers upto row51, hence you get the errors for the remaining rows.

Its current range is $C$4:$AB$57, it should be $C$4:$AB$108

Every time you add more columns you need to extend that named region
Avatar of ChloesDad
ChloesDad
Flag of United Kingdom of Great Britain and Northern Ireland image
Press CTRL C and it will pop up, other answers came in as I was checking mine :(
Avatar of W.E.B
W.E.B

ASKER

Where do I change this?
$C$4:$AB$57, it should be $C$4:$AB$108

thanks
ASKER CERTIFIED SOLUTION
Avatar of FarWest
FarWest
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of FarWest
FarWest
check this from office support page to know more about name range and how to manage them (excel 2010)

https://support.office.com/en-ie/article/Define-and-use-names-in-formulas-2d9abce7-42cf-4a21-a6b4-f02786f690b7
Avatar of W.E.B
W.E.B

ASKER

Ok,
thanks
I edited the formula, I still don't get the price
I get    #REF!
Avatar of W.E.B
W.E.B

ASKER

thank you Farwest,
it's working.
Avatar of ChloesDad
ChloesDad
Flag of United Kingdom of Great Britain and Northern Ireland image
The attached sheet has 150 columns, the lookup table only has 102 rows, so you see data upto column 102 (DA)
Avatar of W.E.B
W.E.B

ASKER

Thank you
Avatar of FarWest
FarWest
you don't have to edit the formula
Sample-EE-jeffld-fixed.xls