If and Loop function

Question

Master Dataset- Sheet1

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.

`"F;F"` is no valid address. It has to be `"F:F"`. Check out if it is just a typo (and correct it please). • Also you should ask a question (there is no question yet). And explain what is wrong with your code. What is the issue? And where did you get stuck or errors? — Pᴇʜ, Mar 20 '20 at 08:03
Is the code "123" is applicable to all sizes of apple or just size 1?. .. I mean, will there be different codes for apple of size 2 and then size 3 ? Also, is the code same even if it is from different countries? — Naresh, Mar 20 '20 at 08:25

Pᴇʜ · Answer 1 · 2020-03-20T08:22:57.553

There is an easy solution without using VBA. You could do that with formulas too with a combination of MATCH() and INDEX(). This should even be faster.

Just use

=INDEX(Sheet1!D:D,MATCH(N:N,Sheet1!F:F,0))

If you need to automate it, I would write this formula into column M (and if necessary convert the formulas into values):

Option Explicit

Public Sub FillInCodes()
    Dim wsSub As Worksheet
    Set wsSub = ThisWorkbook.Worksheets("Sheet2")

    Dim LastRow As Long
    LastRow = wsSub.Cells(wsSub.Rows.Count, "A").End(xlUp).Row

    wsSub.Range("M2:M" & LastRow).Formula = "=INDEX(Sheet1!D:D,MATCH(N:N,Sheet1!F:F,0))"

    'and if you need to convert the formulas into values
    wsSub.Range("M2:M" & LastRow).Value = wsSub.Range("M2:M" & LastRow).Value 
End Sub

If and Loop function

1 Answers1

Linked