Quick Excel VBA Question

[enwar3]

Here's what I got:

Sub Find()
Dim CompareRange As Variant, CompareRange2 As Variant, x As Variant, y As Variant
Set CompareRange = Range("C1:C5")
Set CompareRange2 = Range("E1:E5")
For Each x In CompareRange2
For Each y In CompareRange
If x = y Then
x.Offset(0, 2) = x
Else
x.Offset(0, 4) = x
End If
Next y
Next x
End Sub

What it does is compare every cell in the first range to every cell in the second range. What I need it to do is compare the first cell to the first cell, the second cell to the second, etc. How do I get vba to isolate in rows like that?

 

[xtknight]

I don't quite understand what you mean? Like compare C1 to E1, then C2 to E2, C3 to E3, etc? Are the amount of cells between both always going to be the same?

Then, compare the row ID within that loop.

If (x.Row = y.Row) Then ' C1 matches E1, will proceed. C2 matches E2, etc...
' do what you want here
End If

There might be a more elegant way to do it, but I'm not sure. This way, the Range() really lets you customize it using alphanumeric characters.

 

[enwar3]

Yes!

I want to compare C1 to E1, then C2 to E2, etc.

I'm probably doing something wrong, because I can't get your suggestion to work.

Here's what I tried:

For Each x In CompareRange2
If (x.CompareRange2 = y.CompareRange) Then
x.Offset(0, 2) = x
Else
x.Offset(0, 4) = x
End If
Next x

Am I getting the syntax in the If statement wrong?

 

[xtknight]

CompareRange and CompareRange2 are collections that contain several cells, and they aren't objects of x or y. X and Y, as you go through both of those collections, are used to indicate where in the collection you are. X and Y are where you currently are in the collections, in other words. They're the cell at that particular location in the collections. They are temp variables, but they are still references (pointers) to the actual cells themselves, not just copies of the values. So, when you modify x or y it'll modify the actual cell. Looks like you already knew that.

Here's what I meant:

Sub Find()
Dim CompareRange As Variant, CompareRange2 As Variant, x As Variant, y As Variant
Set CompareRange = Range("C1:C5")
Set CompareRange2 = Range("E1:E5")
For Each x In CompareRange2
For Each y In CompareRange

If (x.Row = y.Row) Then ' did you also mean to compare x=y after this? it looks like you're doing a Find function...
x.Offset(0, 2) = x
Else
x.Offset(0, 4) = x
End If

Next y
Next x
End Sub

 

[xtknight]

A stricter way to define each of those variables:

Dim CompareRange as Range, CompareRange2 as Range
Dim x as Cell, y as Cell

It'll make Intellisense (method lists for each variable as you type) work properly since you're not just defining them as any old object.

 

[enwar3]

Thanks for your help btw!

It won't let me define as a cell.

What exactly does x.row and y.row mean? I need some way to compare C1 to E1, then C2 to E2. Right now it's comparing C1 to E1, E2, E3, E4, E5, then it's comparing C2 to E1, E2, E3.....

The reason I'm doing this is to find discrepancies. I'm going to run this macro down two lists (columns) of numbers. When a row is mismatched, the program is to insert the missing number into the incomplete row to make it complete. I'm working on the part of the program that recognizes a mismatched row.

 

[xtknight]

Originally posted by: enwar3
Thanks for your help btw!

It won't let me define as a cell.

Leave it as a variant, I guess.

What exactly does x.row and y.row mean?

x.Row is the row at which the Cell X currently exists. Likewise for y.

The Row (as far as I know) should be the number in C1, E1, etc.

I need some way to compare C1 to E1, then C2 to E2. Right now it's comparing C1 to E1, E2, E3, E4, E5, then it's comparing C2 to E1, E2, E3.....

I'm definitely aware of that, the If (x.Row = y.Row) should fix that problem.

It's possible I've gotten mixed up and it really should be Column.

 

[enwar3]

I think the For each x and For each y is applying the function to every y cell, instead of just the corresponding one.

 

[xtknight]

Originally posted by: enwar3
I think the For each x and For each y is applying the function to every y cell, instead of just the corresponding one.

Even if you do If x.Row = y.Row? What changes when you put this in?

I can try it here later on but that should have done it.

 

[enwar3]

Ok, I figured out how to do it with an integer substitution...

Dim I As Integer
For I = 1 To 20
If CompareRange.Cells(I) <> CompareRange2.Cells(I) Then
.....

So yea! Life is good =D Thanks your help man!

 

[KLin]

For i = 1 to 50
If ActiveSheet.cells(i, "A") = ActiveSheet.cells(i, "B") Then
....
Next i