vba find values in sheet2 to fit values in sheet1

Question

I have two data ranges:

DataRange is the range in Sheet1 (something like A16:F30)
HoursDataRange is the range in Sheet2 (something like A17: G90).

I need to loop via DataRange rows, take values from row, do some manipluation (like adding those data into seeprate wrokbook), as well, as take corresponding cell in column A and find all rows in Sheet2 (HoursDataRange). I do not know how to do the last thing - find all matching rows in Sheet2.

I am not familiar with VBA yet, so I have read that I can use AutoFilters to filter HoursDataRange or lookups or loops.

What is the best solution?

As I am programmer, I do understand loops, but I am not so familar with VBA.

Here is a shortenedn example of Sheet1:

NAV Nr. CompName    VAT Nr. Adress  Name    Type
101 Int1    1   Address1    Au  1
103 Int2    2   Address2    De  1
210 Int3    3   Address3    Es  1
212 Int4    4   Address4    Sw  1
310 Int5    5   Address5    Fi  1
345 Int6    6   Address6    Fr  1

And here is shortened example of Sheet2:

Key Acc Key NAV Nr. Client  EUR
3   Bu STA  BU  212 Cl1 25,00
4   Bc STA  BC  101 Cl2 25,00
1   Bu STA  BU  212 Cl1 25,00
2   Bc STA  BC  101 Cl2 25,00

So, you see the column Nav Nr. is in both sheets and there are matching values. We can assume that each Nav Nr. occures only once in Sheet1 and I am already looping via Sheet1 DataRange. However, in Sheet2 given Nav Nr can appear from 0 to N times. So I need to find all matching rows (I need to find the whole row and not just cell, because I need manipulations for the all values in rows, for example, Later I would have to sum up EUR column by Nav Nr. and create PDF, but this woudl be later staff. FOr now I just want to know how to find those matching rows.

What you could do is read your data into array variables, running over them as you would in any other language, then write back your results to the respective sheets. This has the very nice side effect that your code is going to run some orders of magnitude faster. — pintxo

Raystafarian Raystafarian · Accepted Answer · 2014-03-20T18:45:11

You'll just want to do exactly as you describe. Here's an example:

Sub renathy()
Dim Cdata As Range
Dim Chour As Range

For Each Cdata In Sheets("Sheet1").Range("A:A")
    If Cdata <> "" Then
        For Each Chour In Sheets("sheet2").Range("A:A")
            If Cdata = Chour Then
            'do stuff
            End If
        Next
    End If
Next
End Sub

vba find values in sheet2 to fit values in sheet1

1 Answers