Macro to Look up items in one spreadsheet, copy certain data related to the item into another spreadsheet

[mikekolba]

Hello,

I Was wondering if anyone could be of assistance with this task and point me in the right direction? Basically I have part numbers and data for items located in one spreadsheet that I want to use to look up the same part number in another spreadsheet, and when it's found, copy certain data found in the row related to that part back to a cell in the original spreadsheet and then move on to the next part number (rinse/repeat) until all the part numbers and items are looked up in the first sheet and have the appropriate matching data copied over from the second sheet.

I haven't done much cross-spreadsheet data transfer before so that's kind of new for me.

Thanks for any assistance or guidance anyone here can provide.

If you need more information, please let me know.

 

[mikekolba]

Any pointers? I'm pretty sure a vlookup won't work based on the data locations in the sheets.

 

[mikekolba]

This is what I have so far on this but i get an error message about a subscript out of range on the following line thats in red. The names of the sheets are correct but i get a subscript out of range error on the one in red for some reason.

Sub GoDaddy_Lookup_PartNumber_Copy_URL_ModelName_to_Long_Short_Description()
Application.ScreenUpdating = False
Dim sh1 As Worksheet, sh2 As Worksheet
Dim j As Long, i As Long, lastrow1 As Long, lastrow2 As Long
Dim cell As Range
Set sh1 = Worksheets("BulkProductData")
Set sh2 = Worksheets("exporturls")
lastrow1 = sh1.Cells(Rows.Count, "B").End(xlUp).Row
lastrow2 = sh2.Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To lastrow1
For j = 2 To lastrow2
If sh1.Cells(i, "B").Value = sh2.Cells(j, "B").Value Then
sh1.Cells(i, "E").Value = sh2.Cells(j, "M").Value And sh1.Cells(i, "F").Value = sh2.Cells(j, "D").Value
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub

Any ideas on why?

Thanks!

 

[mikekolba]

Now I get a type mismatch on this line? Any ideas?

Sub GoDaddy_Lookup_PartNumber_Copy_URL_ModelName_to_Long_Short_Description()
Application.ScreenUpdating = False
Dim sh1 As Worksheet, sh2 As Worksheet
Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long
Set sh1 = Workbooks("www_shop_sensibletechsolutionsllc_com_producttemplate.xlsm").Worksheets("BulkProductData")
Set sh2 = Workbooks("Icecat_export_urls.xlsm").Worksheets("exporturls")
lastrow1 = sh1.Cells(Rows.Count, "B").End(xlUp).Row
lastrow2 = sh2.Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To lastrow1
For j = 2 To lastrow2
If sh1.Cells(i, "B").Value = sh2.Cells(j, "B").Value Then
sh1.Cells(i, "E").Value = sh2.Cells(j, "M").Value And sh1.Cells(i, "F").Value = sh2.Cells(j, "D").Value
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub

 

[mikekolba]

Think I got it. For some reason the lookup column had some errant = signs in it that was causing the error.

 

[mikekolba]

Nope- Don't got it. When running it appears to hang. Is there a good, alternate method for achieving the same thing? The first sheet that I'm trying to copy data into has about 140k rows of data and the 2nd sheet where I'm doing the lookups and copying from has about 60k rows of data...

Any ideas? I'm welcome to suggestions....

 

[mikekolba]

For some details, In the bulkproductdata sheet, I'm using part numbers in column B to try to look up, compare, and find a matching value to part numbers listed in column B in the exporturls sheet.

If there is a match found, then I want to copy the data from that matched row in column M in exporturls to column E in the bulkproductdata sheet. I also want to copy data in the same matched row from column D in exporturls to column F in bulkproductdata

Pls help.

 

[mikekolba]

Any other ideas on this for me?

Also when it's running and looping, is there a way to break out of the macro or do I have to kill excel entirely? Any ideas on debugging what it's doing while it's looping around?

 

[mikekolba]

One thing I noticed- for the icecat_export_urls workbook and sheet, the sheet isn't 60k rows- it's 600k rows. Could that account for the hanging behavior and just mean it takes a really really long time for this to process the macro?

 

[mikekolba]

ok modifed the code a little to include a progress indicator and a break- break didn't work and the progress indicator just appeared to be stuck

This is what I modified the code to be currently. Any ideas on what could be causing the looping and hang?

Sub GoDaddy_Lookup_PartNumber_Copy_URL_ModelName_to_Long_Short_Description()
Application.ScreenUpdating = False
Dim sh1 As Worksheet, sh2 As Worksheet
Dim j As Long, i As Long, lastrow1 As Long, lastrow2 As Long
 
Set sh1 = Workbooks("www_shop_sensibletechsolutionsllc_com_producttemplate.xlsm").Worksheets("BulkProductData")
Set sh2 = Workbooks("Icecat_export_urls.xlsm").Worksheets("exporturls")
lastrow1 = sh1.Cells(Rows.Count, "B").End(xlUp).Row
lastrow2 = sh2.Cells(Rows.Count, "B").End(xlUp).Row
On Error GoTo handleCancel
MsgBox "This may take a long time: press ESC to cancel"
Application.EnableCancelKey = xlErrorHandler
For a = 1 To 135472
Application.StatusBar = 100 * (a / 135472) & " % complete """
For i = 2 To lastrow1
For j = 2 To lastrow2
If sh1.Cells(i, "B").Value = sh2.Cells(j, "B").Value Then
sh1.Cells(i, "E").Value = sh2.Cells(j, "M").Value And sh1.Cells(i, "F").Value = sh2.Cells(j, "D").Value
End If
Next j
Next i
Next a
handleCancel:
If Err = 18 Then
MsgBox "You cancelled"
End If
Application.ScreenUpdating = True
End Sub