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

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.

--
- being bi-polar must suck
- well yes and no

Auric__ wrote:

oercim wrote:

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.

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.

--
Things heard on Hell's P.A. system:
Attention, will the owner of a Pinto, license plate number...