match values from two columns

[elmnas]

Hello people,


I been struggling with a function in couple of days now
I hope someone can help me.


I need a code that loops through sheet "mysheet1" on each row and checks the values in Columns "A" and "B"
Example:


(mySheet1)


Columns:[ A ][ B ]
ROW1: SWE ARA
ROW2: SWE DAN



in this case take value from Column A Row 1 = SWE and Column B Row 1 = ARA
(keep them in memory)


then go to sheet "mysheet2"


check any columns as long as the values in this case "SWE" and "ARA" are placed next to each other.



Could someone help me

[The_Greg]

sub looooooop()
dim x as long, k as long
dim y as string

x=1,
'(first cell is A1)

do until 'write when to stop
mysheet1.activate
y=cells(x,1).value
z=cells(x,2).value

mysheet2.activate
k = 1
do until cells(k,1).value=y and cells(k,2).value = z
k=k+1
loop

'Your code here


loop
end sub


try this

this will work if values in mysheet2 are in columns A and B
Do you have many columns?

[elmnas]

I have many columns also the code doesn't work

Compile error Loop without do,

also "x=1," gives error

Thank you in advance

[elmnas]

I get loads errors have you even run the code you wrote?.

[The_Greg]

nope, I will try something else=)

[The_Greg]

Does it have to be like SWE is in left column and ARA in right or can it also be both ways?

[elmnas]

it can be any value I just gave a example.

[The_Greg]

Sub FIND_PAIRS()
'Application.ScreenUpdating = False
Dim x As Long, y As Long, c As Long, r As Long, k As Long
Dim Lword As String, Rword As String

x = 1
Do Until IsEmpty(Sheets("mysheet1").Cells(x, 1))

Sheets("mysheet1").Activate
Lword = Cells(x, 1).Value
Rword = Cells(x, 2).Value

Sheets("mysheet2").Activate
c = 1
k = 1

Do Until IsEmpty(Cells(k, c)) And Cells(k, c).Row = 1

If c = 1 Then

If Cells(k, c).Value = Lword And Cells(k, c + 1).Value = Rword Then

'YOUR CODE HERE
MsgBox Lword & " and " & Rword & " are on " & k & " row," & vbNewLine & _
" columns are: 1 and " & c + 1
'YOUR CODE HERE

k = k + 1

ElseIf IsEmpty(Cells(k, c)) Then

c = c + 1
k = 1

Else

k = k + 1

End If

ElseIf c > 1 Then

If Cells(k, c).Value = Lword And Cells(k, c + 1).Value = Rword Or _

Cells(k, c).Value = Lword And Cells(k, c - 1).Value = Rword Then

'YOUR CODE HERE
MsgBox Lword & " and " & Rword & " are on " & k & " row," & vbNewLine & _
"one of them is in column: " & c
'YOUR CODE HERE

k = k + 1

ElseIf IsEmpty(Cells(k, c)) Then

c = c + 1
k = 1

Else

k = k + 1

End If

End If

Loop
x = x + 1

Loop
Application.ScreenUpdating = True
End Sub

[elmnas]

Sub FIND_PAIRS()
'Application.ScreenUpdating = False
Dim x As Long, y As Long, c As Long, r As Long, k As Long
Dim Lword As String, Rword As String

x = 1
Do Until IsEmpty(Sheets("mysheet1").Cells(x, 1))

Sheets("mysheet1").Activate
Lword = Cells(x, 1).Value
Rword = Cells(x, 2).Value

Sheets("mysheet2").Activate
c = 1
k = 1

Do Until IsEmpty(Cells(k, c)) And Cells(k, c).Row = 1

If c = 1 Then

If Cells(k, c).Value = Lword And Cells(k, c + 1).Value = Rword Then

'YOUR CODE HERE
MsgBox Lword & " and " & Rword & " are on " & k & " row," & vbNewLine & _
" columns are: 1 and " & c + 1
'YOUR CODE HERE

k = k + 1

ElseIf IsEmpty(Cells(k, c)) Then

c = c + 1
k = 1

Else

k = k + 1

End If

ElseIf c > 1 Then

If Cells(k, c).Value = Lword And Cells(k, c + 1).Value = Rword Or _

Cells(k, c).Value = Lword And Cells(k, c - 1).Value = Rword Then

'YOUR CODE HERE
MsgBox Lword & " and " & Rword & " are on " & k & " row," & vbNewLine & _
"one of them is in column: " & c
'YOUR CODE HERE

k = k + 1

ElseIf IsEmpty(Cells(k, c)) Then

c = c + 1
k = 1

Else

k = k + 1

End If

End If

Loop
x = x + 1

Loop
Application.ScreenUpdating = True
End Sub

You are a ******* genius! thank you so much sir !

[The_Greg]

You are a ******* genius! thank you so much sir !

Glad to help.

[elmnas]

Glad to help.

Hello again,

You have wrote on some places in the code 'YOUR CODE HERE
could you explain what happens on each place?

Thank you in advance.