VBA (which applies VLookup formula to an entire "sheet 1", pulling data from another "sheet 2") when a code is entered in "Sheet 1"

[NateZona]

Dear helpful people of Mr. Excel,

I have been searching for an answer to this problem for quite some time now, to no avail.

I have an Excel 2010 workbook, which keeps track of all the supplies needed for production at my store.
Using a VLookup formula, which is applied to every cell in Column C-Column Q in a sheet called "USED", I am able to populate an entire row, with data from a table in a second sheet called "Sheet 4". This runs when I enter a code word in any cell of Column B.

This works fine, but since "USED" has a formula on so many cells, excel runs slowly whenever opening the workbook, entering data, or saving.

What I am looking for is a way to apply the formula to all the appropriate cells in Columns C-Q in sheet "USED" without putting the formula in all the actual cells themselves.

I believe I can use a VBA to automatically detect when I enter a code into Column B of "USED", and then automatically fill the appropriate data from "Sheet 4" into the appropriate row of "USED". I just can't seem to figure out how!

I am not sure how to attach my Workbook as the "HTML-Maker" section of the FAQ is confusing,
so here is some important data:

Formulas applied respectively to Columns C-Q in sheet "USED"*:
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,2,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,3,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,4,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,5,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,6,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,7,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,8,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,9,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,10,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,11,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,12,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,13,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,14,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,15,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,16,FALSE),"")

*Since each row will change the number of Column B,
"B2" will change to "B3", B4", B5", etc as the row changes.

I hope this was clear enough, and look forward to the solution!

Warm Regards,
Nate Zona

 

[JoeMo]

Here's an untested event macro you can try on a copy of your worksheet. This is sheet code.
To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Target, Columns("B")) Is Nothing Then
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    For Each c In Intersect(Target, Columns("B"))
        c.Offset(0, 1).Formula = "=IFERROR(VLOOKUP(USED!" & c.Address(0, 0) & ",Sheet4!$B$16:$Q$18,COLUMN(B2),FALSE),"""")"
        With c.Offset(0, 1).Resize(1, 15)
            .FillRight
            .Value = .Value
        End With
    Next c
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End If
End Sub

This code will run automatically whenever a change is made to any cell in col B of the sheet where you install the code.

You can remove all the formulas from cols C:Q before you install the code.

 

[NateZona]

Thanks!

One problem, it only populates Column C, when it should be populating Column C-Q.

A step in the right direction though, thank you!

 

[JoeMo]

Thanks!

One problem, it only populates Column C, when it should be populating Column C-Q.

A step in the right direction though, thank you!

It populates C with your formula, then fills right through col Q, then converts all formulas to values. If D:Q are empty after you run the code either the formulas in those columns are returning "" (from the IFERROR wrapper) or you have calculation set to manual. In the latter case add this line between the .FillRight and .Value lines: Me.Calculate

 

[NateZona]

Hmm, still not working correctly. I'm only getting "-184.272" in Column C when I enter the code "2RA" or "2RB" (see table below)

If it helps, Column C:Q are being populated from a table in "Sheet 4" which looks like this:

CODE	STEARIC ACID (g)	EMULSIFYING WAX (g)	HEMP SEED BUTTER (g)	HEMP SEED OIL (g)	COCONUT FRAGRANCE (mL)	“BERRIED TREASURE” FRAGRANCE (mL)	“RAINFOREST” FRAGRANCE (mL)	VITAMIN E (mL)	OPTIPHEN PLUS (mL)	WATER (L)	OLIVE OIL (fl oz)	VERUM ESSENTIAL OIL (mL)	CASSIA ESSENTIAL OIL (mL)	MYRRH ESSENTIAL OIL (mL)	CBD (mL)
2RA	-184.272	-240.971	-340.194	-340.194	-30		-60	-35.4882	-53.2324	-3					-40
2RB	-184.272	-240.971	-340.194	-340.194	-30	-60		-35.4882	-53.2324	-3					-40
4T											-32	-2	-4	-4	-80

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[NateZona]

Also, if it helps, the formulas I have listed in my First Post are each applied to a single column, C through Q respectively.

For example,
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,2,FALSE),"") applies to Column C

=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,3,FALSE),"") applies to Column D

=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,4,FALSE),"") applies to Column E

and so on...

 

[JoeMo]

I think I see the problem. You have not anchored the USED!B2 reference. To fix this change the formula entry line to this:

c.Offset(0, 1).Formula = "=IFERROR(VLOOKUP(USED!" & c.Address & ",Sheet4!$B$16:$Q$18,COLUMN(B2),FALSE),"""")"

 

[NateZona]

THANK YOU SO MUCH!! You are a genius!

 

[NateZona]

One final question....

If I add more columns and rows to the source table on "Sheet 4", do I just reflect those changes in the formula line of the macro? (i.e., if I add two more columns and two more rows, change "$B$16:$Q$18" to "$B$16:$S$20")

And/Or do I need to change anything else to make sure that additional columns are appropriately filled in the destination sheet "USED"?

 

[JoeMo]

One final question....

If I add more columns and rows to the source table on "Sheet 4", do I just reflect those changes in the formula line of the macro? (i.e., if I add two more columns and two more rows, change "$B$16:$Q$18" to "$B$16:$S$20")

And/Or do I need to change anything else to make sure that additional columns are appropriately filled in the destination sheet "USED"?

That should be all you need to do. The "Column(B2)" entry which refers to the lookup column will adjust to the new lookup columns automatically.