Moving/managing data between different worksheets

lufko

New Member
Joined
Jan 13, 2018
Messages
1
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"
AB (val21 column)CDE (val11 column)FGHI (val31 column)J
asd123qwedoneFA/123456123asd123ASDX
asd1234qwerdoneFB/12345123asd123asdX

<tbody>
</tbody>


Example: Worksheet "File2" (the value coming from val11 needs a split to be comparable to val12)
A (val22 column)B (val12 column)CDEF (val32 column)G
123123456aaabbbcccASDzzz
123412345aaabbbcccasdzzz

<tbody>
</tbody>


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. :LOL:):

Code:
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,133
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcell

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,133
Office Version
  1. 365
Platform
  1. Windows
On top of my questions above, this part of your code seems odd
Code:
            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
Code:
            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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,702
Messages
5,524,410
Members
409,575
Latest member
navarrov74

This Week's Hot Topics

Top