Moving/managing data between different worksheets

[lufko]

Hi. I'm a noob in this vba stuff and I need to solve a little problem.

I have a worksheet named "File1" where I have some sales data and the same with a worksheet named "File2".
I have 3 values among all the columns (val1, val2, val3) that I need to compare between the two worksheets.

1. If the 3 values from a row from "File1" are the same with a row from "File2", I need to copy the corresponding row from "File1" to a worksheet named "Result1" and color it green (i'll skip that part from now).
2. If a row with val1 and val3 from "File1" has a corresponding row with those same two values on "File2" worksheet, copy the corresponding row from "File1" to a worksheet named "Result1" and color it yellow.
3. If a row with val2 and val3 from "File1" has a corresponding row with those same two values on "File2" worksheet, copy the corresponding row from "File1" to a worksheet named "Result1" and color it blue.
4. If a row from "File1" with that values does not have a corresponding row on "File2" worksheet, I need to copy that row to a new worksheet named "Result2".

The worksheet "File1" has 744 rows, and the column titles are in the first row.
The worksheet "File2" has 539 rows, and the column titles are in the first row.

Example: Worksheet "File1"
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B (val21 column)[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E (val11 column)[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I (val31 column)[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]asd[/TD]
[TD]123[/TD]
[TD]qwe[/TD]
[TD]done[/TD]
[TD]FA/123456[/TD]
[TD]123[/TD]
[TD]asd[/TD]
[TD]123[/TD]
[TD]ASD[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]asd[/TD]
[TD]1234[/TD]
[TD]qwer[/TD]
[TD]done[/TD]
[TD]FB/12345[/TD]
[TD]123[/TD]
[TD]asd[/TD]
[TD]123[/TD]
[TD]asd[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]


Example: Worksheet "File2" (the value coming from val11 needs a split to be comparable to val12)
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]A (val22 column)[/TD]
[TD]B (val12 column)[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F (val32 column)[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]123456[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD]ccc[/TD]
[TD]ASD[/TD]
[TD]zzz[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]12345[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD]ccc[/TD]
[TD]asd[/TD]
[TD]zzz[/TD]
[/TR]
</tbody>[/TABLE]


This is the code I have to the first point and it's always giving me the subscript out of range on that "var(1)" (maybe i'm just being dumb and doing this in a completely wrong way. help. ):

For lin = 2 To 744
    For lin2 = 2 To 539
        
        'val1, val2 and val3 from File1
        
        val11 = Worksheets("File1").Cells(lin, 5).Value
        var = Split(val11, "/")
        
        val11 = var(1)


        val21 = Worksheets("File1").Cells(lin, 2).Value


        val31 = Worksheets("File1").Cells(lin, 9).Value
    
        
        'val1, val2 and val3 from File2
        val12 = Worksheets("File2").Cells(lin2, 2).Value
        val22 = Worksheets("File2").Cells(lin2, 1).Value
        val32 = Worksheets("File2").Cells(lin2, 6).Value
       
        If val11 = val12 And val21 = val22 And val31 = val32 Then
            
            Dim myrange As Range
            Sheets("File1").Select
            Set myrange = Sheets("File1").Range("B2", Range("B" & Rows.Count).End(xlUp))
            For Each cell In myrange
            If cell.Value = val11 Then
            lr = Sheets("Result1").Range("B" & Rows.Count).End(xlUp).Row
            cell.EntireRow.Copy Destination:=Sheets("Result1").Range("A" & lr + 1)
            End If
            Next cell
                        
        End If
    
    Next lin2
    Next lin

 

[Fluff]

Hi & welcome to MrExcell

How have you declared vall11 & var?
Also what is the value of val11 when it fails?

 

[Fluff]

On top of my questions above, this part of your code seems odd

            Dim myrange As Range
            Sheets("File1").Select
            Set myrange = Sheets("File1").Range("B2", Range("B" & Rows.Count).End(xlUp))
            For Each cell In myrange
            [COLOR=#ff0000]If cell.Value = val11 Then[/COLOR]
            lr = Sheets("Result1").Range("B" & Rows.Count).End(xlUp).Row
            cell.EntireRow.Copy Destination:=Sheets("Result1").Range("A" & lr + 1)
            End If
            Next cell

Because val11 is the numerical part of col E (123456 in your example), but you are comparing it to the value of col B (123 in your example).

Maybe replace that entire block of code with

            With Sheets("Result1").Range("B" & Rows.Count).End(xlUp)
               Sheets("File1").Rows(lin).Copy .Offset(1, -1)
               .Offset(1, -1).EntireRow.Interior.Color = vbGreen
            End With