Vlookup type macro required

[aymeric]

Hi all,

I've never done any macro in my entire life but I got to the point where I'm dealing with excel sheets that have 7k+ line and about 40 columns.
As this is a recurring report type of thing, I'd like to write a macro that would alleviate some of the pain of comparing the new report with the one from the previous month.
It would however need to "draw" from two worksheets.
The idea is that the "batch" (or item name/description) should match exactly in all three worksheet and the "usage decision" is drawn from a mixture of the other two worksheets.

What I need the macro to do is populate worksheet Alpha with the comment attached to that batch found in worksheet Beta ....
I've managed to create a macro to "directly" paste from Beta to Alpha however I do not know/understand how to make it so that the batch is matched to the correct comment (ie I don't want the comment from sm02 to be pasted in the row for sm01 in Alpha or in other word I don't want a straight copy of the cell B2 from Beta into B2 from Alpha).
Let me know if I make sense and whether what I'm trying to do is even possible ...
Another macro that I would be interested in is one that would go through each line and look into a column named quantity, if that column has a value of 0, I'd like to have the comment column to be filled with no stock.

For those batches that were not in the previous report, a simple blank cell would work for me as it would be then much easier for me to just sort for just those and then do my investigation.

Thank you so much in advance for any and all help !!

 

[offthelip]

try this code:

Sub test()
With Worksheets("Beta")
 LastRowB = .Cells(Rows.Count, "A").End(xlUp).Row
 betad = .Range(.Cells(1, 1), .Cells(LastRowB, 2))
End With
With Worksheets("Alpha")
 LastRowA = .Cells(Rows.Count, "A").End(xlUp).Row
 .Range(.Cells(2, 2), .Cells(LastRowA, 2)) = ""
 alphaD = .Range(.Cells(1, 1), .Cells(LastRowA, 2))

 For i = 2 To LastRowB
  For j = 2 To LastRowA
   If betad(i, 1) = alphaD(j, 1) Then
    alphaD(j, 2) = betad(i, 2)
    Exit For
   End If
  Next j
 Next i
 .Range(.Cells(1, 1), .Cells(LastRowA, 2)) = alphaD
 


End With

End Sub

 

[aymeric]

Hi,

thanks, that does work but I'm struggling to understand how it works to amend it from my trial sheet to the actual one ...
the issue I have is that it seems to be pasting the comment to column B but in my actual workbook I need that comment to be pasted in column AM.
The other amendment I'm struggling with is that I need to amend this macro to look into two worksheet name "Let Age Batches" and "all discard list" but VBA doesn't seem to like it when I change the name in that manner ... should I put quotation marks around to solve that issue or something similar ?

as you can see, the match for column I should not be exact match as the comment is not strictly scripted.

So just to summarize, I think I need two different macro (or one combining both) that would draw information from worksheet "let age batches" in column I, to be pasted in worksheet "bmbc" in column AM (for the correct batch (as per column C)) and also draw information from worksheet "all discard list" in column D, to be pasted in worksheet "bmbc" in column AM too (maybe with a "/" separator between the two pieces of information or any separator...

Thanks so much for your help !

 

[offthelip]

I am not clear about your requirements which seem to be a bit confused. To help you I have annotated the code with comments to explain how it works. Also I have modified it to write the comments out into column AM. To do this I introduced another output variant array. which wasn't necessary before because the input array in column A was adjacent to the output in column B, which was what you implied you wanted from your very clear picture at the top . Note this technique is using variant arrays to load all the data into memory, this makes it a very fast to run even if you have thousands of rows. It is much faster that looping through the cells directly.

Sub test2()

With Worksheets("Beta") ' this line specifies that all references with a dot refer to this worksheet. i.e. Beta
LastRowB = .Cells(Rows.Count, "A").End(xlUp).Row ' this find that last cell in column A on Sheet Beta with data in it
betad = .Range(.Cells(1, 1), .Cells(LastRowB, 2)) ' this loads all the data in columns A and B  into a varaint array called betad
End With  ' this ends the scope of the previous WITH refernce
With Worksheets("Alpha") ' this line specifies that all references with a dot refer to this worksheet. i.e. Alpha
LastRowA = .Cells(Rows.Count, "A").End(xlUp).Row ' this find that last cell in column A on Sheet Alpha with data in it
.Range(.Cells(2, 39), .Cells(LastRowA, 39)) = "" ' this clears the data from column AM on the Alpha worksheet
AlphaDout = .Range(.Cells(1, 39), .Cells(LastRowA, 39)) ' this loads the data ( blanks) from column AM on the Alpha workhseet into a variant array called Alphadout
alphaD = .Range(.Cells(1, 1), .Cells(LastRowA, 1)) '' this loads all the data in columns A on sheet Alpha  into a varaint array called Akphadd
' we now to a double loop
     For i = 2 To LastRowB  ' this loops through all the data on worksheet Beta
  For j = 2 To LastRowA ' for each of row of data on worksheet beta will loop through all the rows on worksheet alpha looking for a match
   If betad(i, 1) = alphaD(j, 1) Then ' this checks for a match between column A on Beta and column A on alpha
    ' if a match is found we write the data to the output array
    AlphaDout(j, 1) = betad(i, 2)
    Exit For ' having found the match for this row we exit the loop on Alha and go to the next row on beta
   End If
  Next j  ' Next row on alpha
Next i ' Next row on Beta
.Range(.Cells(1, 39), .Cells(LastRowA, 39)) = AlphaDout ' this writes the output array back tothe workhseet.



End With

End Sub

Note if your worksheet is called bmbc, then change the reference to

With Worksheets("bmbc")