(Solved) – If and Loop function


Master Dataset- Sheet1
Master Dataset- Sheet1

Sub Dataset- Sheet2
Sub Dataset- Sheet2

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.

Leave a Reply

Your email address will not be published. Required fields are marked *