comparing two columns in vba

O

oercim

Hello, I need help again. In excel sheet, I can compare two cells with "exact" command.However in vba, I need to use "exact" command in a loop, and I have to assign column names as variables. For example;

For j=1 To 10
For i = 1 To 100
Sheets("IS1").Cells(i, 4 + 3 * (j - 2)) = "=EXACT(C1;C2 )"
Next
Next

works fine but It compares just 1st and 2nd columns. However, I want to compare for j'th column with j+1'th column. How can I do this. I will be very glad. Thanks a lot.
 
A

Auric__

oercim said:
Hello, I need help again. In excel sheet, I can compare two cells with
"exact" command.However in vba, I need to use "exact" command in a loop,
and I have to assign column names as variables. For example;

For j=1 To 10
For i = 1 To 100
Sheets("IS1").Cells(i, 4 + 3 * (j - 2)) = "=EXACT(C1;C2 )"
Next
Next

works fine but It compares just 1st and 2nd columns. However, I want to
compare for j'th column with j+1'th column. How can I do this. I will be
very glad.

Somewhat like this:

jOffset = 64
For j = 1 To 10
For i = 1 To 100
Sheets("IS1").Cells(i, 4 + 3 * (j - 2)) = "=EXACT(" & Chr(j + _
jOffset) & "1," & Chr(j + jOffset + 1) & "2)"
Next
Next

Set jOffset equal to ASC(UCASE(starting column letter)) - 1. jOffset = 64
will start at column A, jOffset = 65 will start at column B, etc.
 
A

Auric__

Auric__ said:
Somewhat like this:

jOffset = 64
For j = 1 To 10
For i = 1 To 100
Sheets("IS1").Cells(i, 4 + 3 * (j - 2)) = "=EXACT(" & Chr(j + _
jOffset) & "1," & Chr(j + jOffset + 1) &
"2)"
Next
Next

Set jOffset equal to ASC(UCASE(starting column letter)) - 1. jOffset =
64 will start at column A, jOffset = 65 will start at column B, etc.

Ah, hell. My code doesn't account for columns beyond Z. This does...

For j = 1 To 100
For i = 1 To 10
Sheets("IS1").Cells(i, 4 + 3 * (j - 2)).Formula = "=EXACT(" & _
Mid(Cells(i, j).Address, 2, InStr(2, Cells(i, j).Address, _
"$") - 2) & "1," & Mid(Cells(i, j + 1).Address, 2, InStr(2, _
Cells(i, j + 1).Address, "$") - 2) & "2)"
Next
Next

Note that the need for jOffset has been removed. This code is somewhat more
complex, but it doesn't need any tomfoolery to get the column, just grabs it
straight from the Address property.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top