VBA Code, VLookUp, Loop

[GEMINI528]

I have 2 sheet in my workbook, sheet1 is the look up table and sheet2 is where you key the item number to display the result. Right now, I have a formula written on it but every time I add a line item I have to copy the formula and it is running slow. I want to convert it into a VBA code so that only the value is stored on a line item. I need help on writing a VBA code.

Here are the details:
Sheet1 (Look up table)
Row 3 is the Heading
A4, Item No.
B4, Location
C4, On Hand
D4, Status
E4, Description

Sheet2 (Result of lookup table)
Row 4 is the heading
A5:E5, this is where you type an info
F5, this is where you type the Item No. for lookup
G5, this is where the result of the DESCRIPTION lookup from sheet1
H5:N5, this is where you type an info
O5, this is where the result of the STATUS lookup from sheet1
P5, formula to combine C5+D5 (text value)

Then, advance to the next Row (loop)

Thank you in advance for your help.

[AB33]

Well, unless you are going to have an event driven code, you still need to run the code if you want to update it. It is the same with formula.

[GEMINI528]

Thanks for your reply...

Yes, there will be a function to update it, like SHIFT+CTRL+U or any suggestion from your end. I just don't know how to write the code and maybe you can help me.

[JOHN H. DAVIS]

Can you attach a workbook with some sample data?

To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

[Kalithro]

Try this... This assumes Sheet1 is named Sheet1 and Sheet2 is Sheet2. Set this to Worksheet 2, Change, in VBA.
Anytime someone enters an item number in Column 6, the Item number will do Vlookup from Sheet1 for Description and Status. Then C5 and D5 get combined. Then all formulas get changed to values.

Please Login or Register  to view this content.

[GEMINI528]

Thanks Kalithro,

Got and tested your code and it works fine, however, I have few modifications:
1. Sheet1 should be named “DATA”
2. Sheet2 should be named “TRANS”
3. P5, formula to combine “J+K+L+M” (text value) with a dash (“-“) in between
4. The sheet is locked and some cells are locked
5. Sequence of entry is from Column A to P
• A:E- Type info (cell is unlock)
• F – Item No. entry (cell is unlock)
(when you type ITEM NO. , J,K,L,M is still blank, so what will trigger when you enter data on row J,K,L,M to combine it?)
• G – Description (result from Vlookup) (cell is locked)
• H:N – Type info (cell is unlock)
• O – Status (result fromVlookup) (cell is locked)
• P- text result by combining J+K+L+M (cell is locked)

Also, I tried for 5,000 line items and it becomes slow because it calculates every time you enter a new line.

Thank you again and hope to received your revised code.

Kudos to you guys for sharing your talents for those in need...

[GEMINI528]

Hello guys, I need help on the revision of VBA code above. Any help would be appreciated. Thanks