comparing two columns in vba

  • Thread starter Thread starter oercim
  • Start date Start date
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.
 
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.
Click to expand...

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.
 
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.
Click to expand...

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.
 
Back
Top