comparing two columns in vba
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...
|