VLOOKUP with button click

0
votes

I'm trying to write some code for a button click to perform a VLOOKUP.

Sheet1 = Payment Form, Sheet2 = Global, Sheet3 = Details

Button will be on Sheet "Payment Form".

This would be the code for Cells in Global Sheet,

O1 = =VLOOKUP(BA,Details!A:H,8,0)
P1 = =VLOOKUP(BA,Details!A:H,6,0)
Q1 =VLOOKUP(BA,Details!A:H,5,0)

I need this to loop through all rows as the amount can change each month, if a match is found the perform the VlookUp, is no match is found, the delete the row from the Details Sheet.

For Example: Global, Cell B1 = 27801. In Details match found, then do the above codes from Columns O, P & Q. Global, B2 = 27802. In Details no matching record found, row deleted. Continue to row 3 & 4 ......

1
vbaexcelexcel-2010
What have you done in VBA so far? - Sam
@Sam Hi, i have nothing to the moment i don't know where to start, im still very new to VBA - atame
Try recording a macro and see what code do you come up with? - Siddharth Rout
Application.VLOOKUP(lookup_value, table_array, column_index, range_lookup) - 0m3r
@SiddharthRout, this is the code it come out with, i need it loop through all rows. Range("O1").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-13],Details!C[-14]:C[-7],8,0)" Range("P1").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-14],Details!C[-15]:C[-8],6,0)" Range("Q1").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-15],Details!C[-16]:C[-9],5,0)" Range("Q2").Select - atame

1 Answers

0
votes


what I suggest is you put all the value in table will be more efficient (not simply enter in the excel cell), you need create the table by INSERT ->Table. It will look like this : global table
Do this also for the Details :
Details table
Back to global worksheet, just need enter 1 row of formula, the rest of the rows in the same column will have the same formula "style"
Column O :=VLOOKUP([Column BA],Table2[[#All],[Column1]:[Column8]],8,FALSE)
Column P :=VLOOKUP([Column BA],Details!A:H,6,FALSE)
Column Q :=VLOOKUP([Column BA],Details!A:H,5,FALSE)

To remove the unwanted row, just filter out the blanks value in that columns will do.