Help with VBA Macro to similate VLookup - Excel 2013 (15.0.4675.1001)

[hellscheshirecat]

Hi All!

I'm not really sure what to call the code I am hoping for but I will do my very best to describe what I'm looking to do and HOPEFULLY, someone can help me figure it out I have several reports of inventory data that I want to combine automatically by running a macro, so that buyers have the data they need on hand. I don't want my employees to have to mess with formulas and having to drag values.

Right now my workbook (file name MrExcelHelp.xlsm) has four sheets in it "Master2","On Hand","Jan 14","RJan 14"... but when complete there will be many many more sheets with data from additional months (all of 2014 and will have 2015 data added)

Master2 is where I want to gather all the data into
On Hand has a report of what inventory is in stock based on SKU
Jan 14 has the sales data for the month of January 2014 based on ASIN
RJan 14 is the record of what the warehouse received during January 2014

My main reasons for not just using formulas are:
1) fear that someone will mess them up without realizing it and then place orders based on bad data *this is a big one*
2) the number of items will change every time the "On Hand" sheet is updated (daily)
3) likewise, during the month we are currently in that months sheets will change regularly

The Master Sheet has 8 columns (for now) and the headers are in row 2 (I have instructions in row 1) It looks like this:

[TABLE="class: grid, width: 600, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]ITEMS IN RED ARE DUPLICATES
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]YELLOW IS IN STOCK
[/TD]
[TD]GREEN HAS QTY SOLD

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]sku[/TD]
[TD]asin
[/TD]
[TD]product-name
[/TD]
[TD]condition
[/TD]
[TD]your-price
[/TD]
[TD]FBAINV
[/TD]
[TD]JAN2014
[/TD]
[TD]REC JAN2014
[/TD]
[/TR]
</tbody>[/TABLE]










I have macros to pull the data from On Hand into Master 2, and a macro to run them all at once:

Sub runallmacros()
skusfromonhand
asinsfromonhand
titlefromonhand
conditionfromonhand
pricefromonhand
qtyfromonhand
End Sub
Sub skusfromonhand()

    Dim lastRow As Long
    lastRow = Sheets("On Hand").Range("A" & Rows.Count).Row
    Sheets("Master2").Range("A3:A" & lastRow).Value = Sheets("On Hand").Range("A2:A" & lastRow).Value

End Sub
Sub asinsfromonhand()

    Dim lastRow As Long
    lastRow = Sheets("On Hand").Range("C" & Rows.Count).Row
    Sheets("Master2").Range("B3:B" & lastRow).Value = Sheets("On Hand").Range("C2:C" & lastRow).Value

End Sub
Sub titlefromonhand()

    Dim lastRow As Long
    lastRow = Sheets("On Hand").Range("D" & Rows.Count).Row
    Sheets("Master2").Range("C3:C" & lastRow).Value = Sheets("On Hand").Range("D2:D" & lastRow).Value

End Sub
Sub conditionfromonhand()

    Dim lastRow As Long
    lastRow = Sheets("On Hand").Range("E" & Rows.Count).Row
    Sheets("Master2").Range("D3:D" & lastRow).Value = Sheets("On Hand").Range("E2:E" & lastRow).Value

End Sub
Sub pricefromonhand()

    Dim lastRow As Long
    lastRow = Sheets("On Hand").Range("F" & Rows.Count).Row
    Sheets("Master2").Range("E3:E" & lastRow).Value = Sheets("On Hand").Range("F2:F" & lastRow).Value

End Sub
Sub qtyfromonhand()

    Dim lastRow As Long
    lastRow = Sheets("On Hand").Range("K" & Rows.Count).Row
    Sheets("Master2").Range("F3:F" & lastRow).Value = Sheets("On Hand").Range("K2:K" & lastRow).Value

End Sub

It's not the most elegant but it's what I was able to piece together. I do have one issue with this... IF for example the first time the macro was ran there were 30 rows of data, if there are less rows (lets say 20) the next time the macro is run it will replace the data in rows 1-20 but leave rows 20-30... I'd like it to delete them.

The data for Column G on Master2 needs to come from Sheet "Jan 14", column E, header is in row 1 (Units Ordered). The connecting data for this in column A (Asin).

The "Jan 14" sheet looks like this:
[TABLE="class: grid, width: 600, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Asin[/TD]
[TD]Title
[/TD]
[TD]SKU
[/TD]
[TD]Buy Box Percentage
[/TD]
[TD]Units Ordered
[/TD]
[/TR]
</tbody>[/TABLE]




So If I were to use a formula, on Sheet "Master2", in cell G3 I would write =IFERROR(VLOOKUP(B3,'Jan 14'!$A$1:$E$17,5,FALSE),"0") and then copy it down to all the rows that had data.

Lastly, The data for Column H on Master2 needs to come from Sheet "RJan 14". The data I want to pull is from Column E (quantity), the connecting data is in column C (sku)... BUT... the skus may repeat on multiple rows (meaning they may have been recieved into the warehouse more than once in a month) so I would want the values combined. (ex. SKU1... 6 peices were recieved jan 10th, and 4 peices were recieved Jan 20th, so I would want the returned value for SKU1 to be 10)

"RJan14" sheet looks like this:
[TABLE="class: grid, width: 600, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]received-date
[/TD]
[TD]fnsku
[/TD]
[TD]sku
[/TD]
[TD]product-name
[/TD]
[TD]quantity
[/TD]
[TD]fba-shipment-id
[/TD]
[TD]fulfillment-center-id
[/TD]
[/TR]
</tbody>[/TABLE]





If I were using a formula I would use =IFERROR(VLOOKUP(A3,RJan14!C2:G24,3,FALSE),"0"), then copy it down but that wouldn't account for the duplicate values.

So... what I think I need is two macros, one to pull the data from sheet "Jan 14" and one to pull the data from sheet "RJan 14" and combine the values... or one crazy macro that combines that with the macros I already have.

It would be great too if you could help me understand how to do it myself. Thank you so very, very much for reading all that. I hope someone can help me!

 

[Michael M]

Don't have excel at the moment, but this should shorten your code and do the VLOOKUP for the master sheet

Sub MM1Import()
    Dim lastRow As Long
    lastRow = Sheets("On Hand").Range("A" & Rows.Count).Row
    lr2 = Sheets("Master").Range("A" & Rows.Count).Row
    Sheets("Master").Range("A3:F" & lr2).ClearContents
    Sheets("On Hand").Range("A2:F" & lastRow).Copy Destination:=Sheets("Master2").Range("A3")
     lr2 = Sheets("Master").Range("A" & Rows.Count).Row
   Sheets("Master").Range("G3:G" & lr2).Formula = "=IFERROR(VLOOKUP(B3,'Jan 14'!$A$1:$E$17,5,FALSE),""0"")"
End Sub

 

[hellscheshirecat]

Hi Michael!

Thank you so much for responding. I just tried the code you provided but I am getting the following error:

Run-time error '9':
Subscript out of range

When I click de-bug the row that is highlighted is

lr2 = Sheets("Master").Range("A" & Rows.Count).Row

 

[Michael M]

Oooops sorry....typo

Sub MM1Import()
    Dim lastRow As Long, lr2 as Long
    lastRow = Sheets("On Hand").Range("A" & Rows.Count).Row
    lr2 = Sheets("Master2").Range("A" & Rows.Count).Row
    Sheets("Master2").Range("A3:F" & lr2).ClearContents
    Sheets("On Hand").Range("A2:F" & lastRow).Copy Destination:=Sheets("Master2").Range("A3")
     lr2 = Sheets("Master2").Range("A" & Rows.Count).Row
   Sheets("Master2").Range("G3:G" & lr2).Formula = "=IFERROR(VLOOKUP(B3,'Jan 14'!$A$1:$E$17,5,FALSE),""0"")"
End Sub

 

[hellscheshirecat]

Hi Michael, the previous error is gone, but now there is a new one.

Run-time error '1004':
We can't paste because the Copy area and paste area aren't the same size.
Try one of the following:
Click one call, then paste.
Select a rectangle that's the same size, then paste.

I've tried running it with different cells selected. I got the same error when A3 was selected, with A3:H30 and with the entire sheet selected.

Thanks again for all your help!
Jennifer

 

[ndsutherland]

I haven't read your code closely, but I think lastrow is returning 1048576 which is the last row in excel '13. Try

lastRow = Sheets("On Hand").Range("A" & Rows.Count).End(xlup).Row

and the same addition for lr2

 

[hellscheshirecat]

Hi ndsutherland,

Thanks! That did seem to resolve the run-time error. But now I can see there are some issues in the macro. It is pulling the data from the "On Hand" Sheet but it's pulling an extra column. I need it to skip over column B (fnsku) when it pulls it into the "Master2" sheet. So it's not a straight copy/paste... maybe it would be two separate ones? One to copy over column A and then one to copy column C:G.

The other issue, the data is supposed to come in starting in row 3 (I have instructions in row 1 and headers in row 2), and while the data is coming into the right place, the first time I run the macro it erases the headers in row 2.

Also, it isn't combining and pulling data from sheet RJan14.

Thanks so much!!!

Jennifer

 

[ndsutherland]

Try

Sub MM1Import()
    Dim lastRow As Long, lr2 As Long, lr3 As Long
    lastRow = Sheets("Master2").Range("A1").SpecialCells(xlLastCell).Row
    lr2 = Sheets("On Hand").Range("A1").SpecialCells(xlLastCell).Row
    lr3 = Sheets("RJan 14").Range("A1").SpecialCells(xlLastCell).Row
    
    Sheets("Master2").Range("A3:A" & lastRow).ClearContents
    Sheets("Master2").Range("C3:H" & lastRow).ClearContents
    
    Sheets("On Hand").Range("A2:A" & lr2).Copy Destination:=Sheets("Master2").Range("A3")
    Sheets("On Hand").Range("C2:F" & lr2).Copy Destination:=Sheets("Master2").Range("C3")
    
    Sheets("RJan 14").Range("H2:H" & lr3).Formula = "=sumproduct((C$2:C$" & lr3 & "=C2)*E$2:E$" & lr3 & ")"
    Sheets("Master2").Range("G3:G" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,'Jan 14'!$C:$E,3,FALSE),0)"
    Sheets("Master2").Range("H3:H" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,'RJan 14'!$C:$H,6,FALSE),0)"
End Sub

I am assuming that RJan 14 Column H is availabe. If you don't want to create an extra column, or this isn't what you are trying to do, let me know.

 

[hellscheshirecat]

That's awesome!! Thank you so very, very much!

Right now my sheet only has data from one month (for testing), how would I add to this macro to include pulling in data from additional sheets? (Feb14 and RFeb14 for example)

Jennifer

 

[ndsutherland]

Where do you want to put the new months data in the master sheet. Do you want to use additional columns, rows, or just a running subtotal in columns G and H?