Similar to the question on If and Loop function to extract data, I have two worksheets. I am trying to use VBA to input Column M for me- the x’s. For example, to the left of apple should be 123, as it is its code, and orange 456 etc., according to the Master Dataset. Because it is a similar problem as the one on the aforementioned site, I tweaked the code a little, but it would not work. It is as follows:
Option Compare Text Sub DataExtraction() Dim SrchRng As Range, cel As Range, rngDest as Range Dim ws1 As Worksheet, ws2 As Worksheet Set ws1 = Worksheets("Sheet1") Set ws2 = Worksheets("Sheet2") 'restrict the search range Set SrchRng = Application.Intersect(ws1.Range("F;F"), ws1.UsedRange) Set rngDest = ws2.cells(rows.count, 1).end(xlUp).Offset(1, 0) 'start copy here For Each cel In SrchRng.Cells If cel.value=rngDest.value Then rngDest.offset(0, -1).value = cel.offset(0, -1).value Set rngDest = rngDest.offset(1, 0) '<< next row down End If Next cel End Sub
In short, I am trying to tell VBA that if the Fruit of interest matches, then input the Code found in Column D of Sheet1 into Column M of Sheet 2 accordingly, then move to the next row and repeat the exercise. Any help would be greatly appreciated.
P.S. A very special thanks to Tim Williams for solving my problem previously, and hitherto helping me to set up this model that I used to develop.