From the following image above I want to create a macro that does the following:
What I want is to make a cycle that goes through the rows of my data: compare row1 of column A with row1 of column G and compare row1 of column B with row1 of column I if this is true it stores the value in a variable from row1 to column H
and advance to the next row in columns G, H, I. Now it does the same comparison row1 A with row 2 G and row 1 B with row2 I and if true adds the value of the previous column H with the current one and so on successively.
Namely
-8403 = -8403 AND 1 = 1 Then var = 9
-8403 = -8403 AND 1 = 1 Then var = 9 + 11
I have the following code that compares the holdings of column A to column G and it works perfectly for me but now I want to compare more columns.
Sub ObtenerCoincidencias()
Dim ClaveCat As Range
Dim ClaveExi As Range
Dim x As Object
Dim y As Object
Set ClaveExi = Range("A2:A18")
Set ClaveCat = Range("G2:G350")
For Each x In ClaveExi
For Each y In ClaveCat
If x = y Then
y.Interior.Color = RGB(255, 204, 0)
End If
Next y
Next x
End Sub
Any suggestions to improve my code and achieve the task I require, thank you very much in advance.
There is another less complicated solution than VBA and it uses all the power of Excel and Relational Databases.
[CLAVE]&[NUM ALMACEN]
in each table.[id]
as the common key of the two tables.I hope I have understood well.
I did the traversal in a different way so that your data ranges are not fixed.
With the data extract of the image that I attach the result of the variable (var) that is increasing gave 20.
Here the code:
End Sub