Loop or condition? To check down the rows

[KH_GS]

Hi All,

I need to match words within a column, for the first 3 columns.

I have the following code done, however, this will not work if there is a blank row in between the list of words that I have in the column.

x = ActiveCell.Row
y = ActiveCell.Column

Do While Cells(x + 1, y - 1).Value <> ""

'to input EXACT formula
ActiveSheet.Cells(x, y).FormulaR1C1 = "=EXACT(RC[-1],R[1]C[-1])"

x = x + 1

Loop

I cannot sort columns 2 and 3 alphabetically because it will distort the data in adjacent columns.

How can I go about matching the words down the column skipping blank rows and check with the next non-blank cell?

[Greg Wilson]

Try:

Sub Test()
Dim r As Range, c As Range
Dim rw As Long, col As Long
Dim ws As Worksheet

Set ws = ActiveSheet
rw = ActiveCell.Row
col = ActiveCell.Column
Set r = ws.Range(ws.Cells(rw + 1, col - 2), _
ws.Cells(Rows.Count, 1).End(xlUp))
For Each c In r.Cells
If Not IsEmpty(c) Then c(0, 3).FormulaR1C1 = _
"=EXACT(RC[-1],R[1]C[-1])"
Next
End Sub

I didn't follow what you meant by sorting distorting the data in adjacent
columns. If you don't expand the selection to include these columns then it
should only sort selected or specified columns. Or am I missing something ?

Regards,
Greg

"KH_GS" wrote:

>
> Hi All,
>
> I need to match words within a column, for the first 3 columns.
>
> I have the following code done, however, this will not work if there is
> a blank row in between the list of words that I have in the column.
>
> x = ActiveCell.Row
> y = ActiveCell.Column
>
> Do While Cells(x + 1, y - 2).Value <> ""
>
> 'to input EXACT formula
> ActiveSheet.Cells(x, y).FormulaR1C1 = "=EXACT(RC[-1],R[1]C[-1])"
>
> x = x + 1
>
> Loop
>
> I cannot sort columns 2 and 3 alphabetically because it will distort
> the data in adjacent columns.
>
> How can I go about matching the words down the column skipping blank
> rows and check with the next non-blank cell?
>
>
> --
> KH_GS
> ------------------------------------------------------------------------
> KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920
> View this thread: http://www.excelforum.com/showthread...hreadid=527404
>
>

[KH_GS]

Hi

There's unique serial number tag to each row. That is where if I sort only the selected column, it will not match the number in the same row.

Example:

1234 Green ball blue stripes
9876 Red green blue yellow

so if i sort selected column with "stripes" and "yellow", it will no longer match the serial number. and if i sort the whole chunk, the EXACT formula in the other columns will be distorted as my code checks only current cell and the row below.

I was hoping to get help on coding something that could do the iteration thru the whole column(which in this case can handle the blank cells as well) instead of my method of matching with the cell directly below it.

Hope that is clear.

Try:

Sub Test()
Dim r As Range, c As Range
Dim rw As Long, col As Long
Dim ws As Worksheet

Set ws = ActiveSheet
rw = ActiveCell.Row
col = ActiveCell.Column
Set r = ws.Range(ws.Cells(rw + 1, col - 2), _
ws.Cells(Rows.Count, 1).End(xlUp))
For Each c In r.Cells
If Not IsEmpty(c) Then c(0, 3).FormulaR1C1 = _
"=EXACT(RC[-1],R[1]C[-1])"
Next
End Sub

I didn't follow what you meant by sorting distorting the data in adjacent
columns. If you don't expand the selection to include these columns then it
should only sort selected or specified columns. Or am I missing something ?

Regards,
Greg

"KH_GS" wrote:

>
> Hi All,
>
> I need to match words within a column, for the first 3 columns.
>
> I have the following code done, however, this will not work if there is
> a blank row in between the list of words that I have in the column.
>
> x = ActiveCell.Row
> y = ActiveCell.Column
>
> Do While Cells(x + 1, y - 2).Value <> ""
>
> 'to input EXACT formula
> ActiveSheet.Cells(x, y).FormulaR1C1 = "=EXACT(RC[-1],R[1]C[-1])"
>
> x = x + 1
>
> Loop
>
> I cannot sort columns 2 and 3 alphabetically because it will distort
> the data in adjacent columns.
>
> How can I go about matching the words down the column skipping blank
> rows and check with the next non-blank cell?
>
>
> --
> KH_GS
> ------------------------------------------------------------------------
> KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920
> View this thread: http://www.excelforum.com/showthread...hreadid=527404
>
>

[KH_GS]

Hi Greg

Attached is a screenshot sample of the data. The top portion was my initial code. The bottom portion is the result of your code. Perhaps with a picture you get a clearer idea of what I meant and what I intend to do.

Try:

Sub Test()
Dim r As Range, c As Range
Dim rw As Long, col As Long
Dim ws As Worksheet

Set ws = ActiveSheet
rw = ActiveCell.Row
col = ActiveCell.Column
Set r = ws.Range(ws.Cells(rw + 1, col - 2), _
ws.Cells(Rows.Count, 1).End(xlUp))
For Each c In r.Cells
If Not IsEmpty(c) Then c(0, 3).FormulaR1C1 = _
"=EXACT(RC[-1],R[1]C[-1])"
Next
End Sub

I didn't follow what you meant by sorting distorting the data in adjacent
columns. If you don't expand the selection to include these columns then it
should only sort selected or specified columns. Or am I missing something ?

Regards,
Greg

"KH_GS" wrote:

>
> Hi All,
>
> I need to match words within a column, for the first 3 columns.
>
> I have the following code done, however, this will not work if there is
> a blank row in between the list of words that I have in the column.
>
> x = ActiveCell.Row
> y = ActiveCell.Column
>
> Do While Cells(x + 1, y - 2).Value <> ""
>
> 'to input EXACT formula
> ActiveSheet.Cells(x, y).FormulaR1C1 = "=EXACT(RC[-1],R[1]C[-1])"
>
> x = x + 1
>
> Loop
>
> I cannot sort columns 2 and 3 alphabetically because it will distort
> the data in adjacent columns.
>
> How can I go about matching the words down the column skipping blank
> rows and check with the next non-blank cell?
>
>
> --
> KH_GS
> ------------------------------------------------------------------------
> KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920
> View this thread: http://www.excelforum.com/showthread...hreadid=527404
>
>

[Greg Wilson]

I hard coded the column as "1" instead of "col -2" in the second part of the
range definition by mistake. My testing was in column A so I didn't pick up
on it:

Set r = ws.Range(ws.Cells(rw + 1, col - 2), _
ws.Cells(Rows.Count, 1).End(xlUp))

Should be:

Set r = ws.Range(ws.Cells(rw + 1, col - 2), _
ws.Cells(Rows.Count, col - 2).End(xlUp))


Does the following corrected code help?

Sub Test()
Dim r As Range, c As Range
Dim rw As Long, col As Long
Dim ws As Worksheet

Set ws = ActiveSheet
rw = ActiveCell.Row
col = ActiveCell.Column
Set r = ws.Range(ws.Cells(rw + 1, col - 2), _
ws.Cells(Rows.Count, col - 2).End(xlUp))
For Each c In r.Cells
If Not IsEmpty(c) Then c(0, 3).FormulaR1C1 = _
"=EXACT(RC[-1],R[1]C[-1])"
Next
End Sub

Regards,
Greg