Check sheet1 after cells in Column A & B same row values anywhere in sheet2 as long they same ro 1

[Xsi]

Hello people,

I have made following code,

the code check in sheet1 columns "A"and "B" then match them with sheet2 columns "A" and "B".

instead I would like the code to check if

sheet1 columns "A" and "B" cells same row values are anywhere in sheet2, instead of sheet2 "A" and "B" as long they are same row.

here is code

Sub Compare()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim r As Long
    Dim m As Long
    Dim s As Long
    Dim n As Long
    Dim mystring As String
    Dim mystringtwo As String
    Dim myresult As String

    
        
  
    Set ws1 = ActiveSheet
    m = ws1.range("A" & ws1.Rows.Count).End(xlUp).Row
    
    Set ws2 = Worksheets("sheet2")
    n = ws2.range("A" & ws2.Rows.Count).End(xlUp).Row
    
    For r = 1 To m
    For s = 1 To n
    
    If Trim(ws1.range("A" & r)) <> "" Or Trim(ws2.range("B" & r)) <> "" Then
        If ws1.range("A" & r) = ws2.range("A" & s) And _
           ws2.range("B" & r) = ws2.range("B" & s) Then

            mystring = ws2.range("G" & s).Interior.ColorIndex = 5
            
            mySheetStringone = ws2.range("G" & s).Text
            mystring = Sheets("sheet2").range("G" & s).Text
            mystringtwo = Sheets("sheet1").range("J" & s).Text
            myresult = (mystring * mystringtwo)
            Sheets("Sheet2").range("N" & s).Value = myresult
      
        End If
    End If
Next s
Next r
End Sub

See my attached file


Could someone help me

thank you in advance

 

[SkipVought]

hi,

Your example does not make sense since there is no data in Sheet2 other than what is in columns A & B?????

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Xsi]

there is data in Sheet2,

 

[SkipVought]

I didn't say there wasn't. But the in "instead of sheet 2 A & B As long as they are same row" there is none.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Xsi]

Oh sorry if I am not clear enough I mean,

if you see on sheet1,

I need a code that

check each row in sheet "sheet1" column A and B

example:
first row sheet "sheet1"

Column A: Column B:
USA ARA

check after USA and ARA in Sheet2 two
when found take value from column G sheet "sheet2" same row in this case "0.09" * sheet1 column J same row in this case "2" set the new value in sheet1 same row but column N

 

[strongm]

Sheet1 in the file you provide does NOT have any data in column J

 

[zelgar]

If you're wanting to know if your data in Row X is the same in Sheet 1 & Sheet 2 make the following changes:
1. Get rid of the s loop:
2. Replace all references to s to r:

Sub Compare()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim r As Long
    Dim m As Long
    Dim n As Long
    Dim mystring As String
    Dim mystringtwo As String
    Dim myresult As String
  
    Set ws1 = ActiveSheet
    m = ws1.range("A" & ws1.Rows.Count).End(xlUp).Row
    
    Set ws2 = Worksheets("sheet2")
    
    For r = 1 To m
    
    If Trim(ws1.range("A" & r)) <> "" Or Trim(ws2.range("B" & r)) <> "" Then
        If ws1.range("A" & r) = ws2.range("A" & r) And _
           ws2.range("B" & r) = ws2.range("B" & r) Then

            mystring = ws2.range("G" & r).Interior.ColorIndex = 5
            
            mySheetStringone = ws2.range("G" & r).Text
            mystring = Sheets("sheet2").range("G" & r).Text
            mystringtwo = Sheets("sheet1").range("J" & r).Text
            myresult = (mystring * mystringtwo)
            Sheets("Sheet2").range("N" & r).Value = myresult
      
        End If
    End If
Next r
End Sub

 

[strongm]

>set the new value in sheet1 same row but column N

Your code suggests sheet 2 column N.

So, for example, based on your code:

[blue]Sub Compare()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim r As Long
    Dim m As Long
    Dim s As Long
    Dim n As Long

    Set ws1 = Worksheets("sheet1")
    m = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row
    
    Set ws2 = Worksheets("sheet2")
    n = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row
    
    For r = 1 To m
        For s = 1 To n
            If Trim(ws1.Range("A" & r) & ws1.Range("B" & r)) = Trim(ws2.Range("A" & s) & ws2.Range("B" & s)) Then
                    ws2.Range("N" & s).Value = Val(ws1.Range("J" & s)) * Val(ws2.Range("G" & s))
            End If
        Next s
    Next r
End Sub[/blue]

 

[Xsi]

the code but I dont get the result I want I will try explain better sorry..


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.

 

[SkipVought]

...and then???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Xsi]

just msgbox the cells that are equals to

 

[strongm]

>when found take value from column G sheet "sheet2" same row in this case "0.09" * sheet1 column J same row in this case "2" set the new value in sheet1 same row but column N
>just msgbox the cells that are equals to

Please make up your mind what your requirement actually is.

 

[SkipVought]

just msgbox the cells that are equals to"

Equal to WHAT? You surely do not want to answer a MsgBox for hundreds of matches, do you really? You answer and then this information is GONE! There is no opportunity to interact with the sheet.

Listen, English may not be your primary language, and we understand that. We are being very patient with you.

Please think very carefully about what it is that you need this code to perform. Requirements and statements of work ought to be very detailed in order to convey the information that is necessary to write a procedure that performs properly. It must be clear concise and complete.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[strongm]

Here's code that illustrates Skip's point about the hundreds of messageboxes (and you'll note it is pretty much exactly the same as the code already posted)

[blue]Sub Compare()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim r As Long
    Dim m As Long
    Dim s As Long
    Dim n As Long

    Set ws1 = Worksheets("sheet1")
    m = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row
    
    Set ws2 = Worksheets("sheet2")
    n = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row
    
    For r = 1 To m
        For s = 1 To n
            If Trim(ws1.Range("A" & r) & ws1.Range("B" & r)) = Trim(ws2.Range("A" & s) & ws2.Range("B" & s)) Then
                    MsgBox "Cells " & "A" & r & " " & "B" & r & " on Sheet1 contain " & ws1.Range("A" & r) & " " & ws1.Range("B" & r) & " which matches A" & s & " " & "B" & s & " on Sheet2"
                    [green]'ws2.Range("N" & s).Value = Val(ws1.Range("J" & s)) * Val(ws2.Range("G" & s))[/green]
            End If
        Next s
    Next r
End Sub[/blue]

 

[Xsi]

Sorry for a late answer,

Thank you very much!! strongm!!

that was exactly what I was looking for I did some modifications just:

Sub Compare()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim r As Long
    Dim m As Long
    Dim s As Long
    Dim n As Long

    Set ws1 = Worksheets("Volvo_Statistik")
    m = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row
    
    Set ws2 = Worksheets("volvo_NewPrices")
    n = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row
    
    For r = 1 To m
        For s = 1 To n
            If Trim(ws1.Range("A" & r) & ws1.Range("B" & r)) = Trim(ws2.Range("A" & s) & ws2.Range("B" & s)) Then
    
                  ws1.Range("N" & r).Value = Val(ws1.Range("J" & r)) * Val(ws2.Range("G" & s))
                    
                     
                 
                 
            End If
        Next s
    Next r
End Sub