asked on

vba routine to post values from one sheet to another

vba routines needed: no formulas on sheet.

What I have:
Column Headers on sheet1 and sheet2..ON BOTH SHEETS THE HEADERS RUN A1 TO I1
In a Nutsheel on Sheet1 from Cell a2 to whatever I have my lookup values. stored as text.

The values on sheet1 ColumnA may lookup like
223aw
33w1234
ss21qw2
etc...

I have on Sheet2 in ColumnA the value I'm looking for.

What I need:
Take the value(s) in ColumnA on sheet1 and look into Sheet2 ColumnA and return the Values in ColumnS B TO I from Sheet2 and place those values in Column B TO I on sheet1 in the same row.

Running this from a command button. Processing all the values at once in a batch mode.

Thanks
fordraiders

Zack Barresse

Hi there,

Why don't you convert your data range to a structured table? then input a formula one time and have them auto-populate to the number of rows you have (on Sheet1)? I understand how you may want to do this with 'the click of a button', but I'm not sure it's the best method.

To give you a structured table referenced formula, we'd need to know your column headers (text), but an example would look like this....

'** Assumes 'Sheet1' table is named 'Table1' and 'Sheet2' table is named 'Table2'
'** Also that column 1 (A) of both tables are named 'VALUE'.
'** Also that the tables on both sheets start in the same column, i.e. col A

In B2 of Sheet1 (or Table1, second column, first row)
=INDEX(Table2, MATCH([@VALUE],Table2[VALUE],0), COLUMN())

You cannot drag this formula to the right after you confirm it, you MUST copy it and paste it to the remaining columns (B to I). Once you copy these across, select all of these formulas (B2:I2) and double-click the bottom-right corner of the selection (your cursor will turn into a bold plus '+' sign).

If this isn't what you're looking for, can you let us know?

HTH,
Zack Barresse

ASKER CERTIFIED SOLUTION

terencino

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Fordraiders

ASKER

I have to have vba routines. People delete things..I prefer not to have formulas on the sheet itself please.
Thanks
fordraiders

Fordraiders

ASKER

Thanks