Help to Compare with 4 variables

[Blichfeldt]

Hi there

I'm trying to make a seach function based on compare.
It' just does'nt work.
Is it because compare doesn't support multible variables.
If yes, what should I use insted?
This is what I've made

Sub Test()


    Dim CompareRange As Variant, A As Variant, B As Variant, X As Variant, Y As Variant


     Sheets("123").Select
     Range("B2:Z2").Select and Range("A3:A10")
      ' ("B2:Z2") is selection A
      ' ("A3:A10") is selction X
     

    Set CompareRange = Workbooks("ABC").Worksheets("456").Range("B3:B260") and Range("C3:C260")
      ' ("B3:B260") is CompareRange B
      ' ("C3:C260") is CompareRange Y

    For Each A In Selection
        For Each B In CompareRange

    For Each X in selction
        For Each Y in CompareRange


            If A = B And B <> 0 And X = Y and Y <> 0 Then X.Offset(0, 1) = B.Offset(0, -1)

        Next B
     Next A
        Next Y
     Next X
End Sub

Thanks to aelgadi

[aelgadi]

First of all you should use CodeTags as per Forum Rule 3
3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Select your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button). The result will appear like this in the post window:


... and appear like this when posted:

 your code here ...
 and here ...
 and here

and hereYou can also type the code tags in manually if you prefer. For more information about these and other tags, click here.

[aelgadi]

Try this

Sub Test()
    Dim Range_A As Range
    Dim Range_X As Range
    Dim aBool As Boolean
    Dim xBool As Boolean
    Dim CompareRange_B As Variant, CompareRange_Y As Variant, A As Variant, B As Variant, X As Variant, Y As Variant
    aBool = False
    xBool = False
    Sheets("123").Select
    Set Range_A = Range("B2:Z2")    '("B2:Z2") is selection A
    Set Range_X = Range("A3:A10")    '("A3:A10") is selction X
    Set CompareRange_B = Workbooks("ABC.xlsm").Worksheets("456").Range("B3:B260") '("B3:B260") is CompareRange B
    Set CompareRange_Y = Workbooks("ABC.xlsm").Worksheets("456").Range("C3:C260") '("C3:C260") is CompareRange Y
    
    Range_A.Select
    For Each A In Selection
        For Each B In CompareRange_B
            If A = B And B <> 0 Then aBool = True
        Next B
    Next A
    
    
    Range_X.Select
    For Each X In Selection
        For Each Y In CompareRange_Y
            If X = Y And Y <> 0 Then xBool = True
        Next Y
    Next X
    If aBool = True And xBool = True Then X.Offset(0, 1) = B.Offset(0, -1)
End Sub

aelgadi

>>> If I helped, don't forget to add to my reputation (click on the at left bottom of the post)
>>> If satisfied with a solution, don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

[Blichfeldt]

It helped a lot, but I stiil have problems with offset.
One reason is my fault, the definition istn't clear.
The offset should be in the cross og the compare, not as an offset.
Other reason I dont know. The code fails in offset.