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 i
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 distor
the data in adjacent columns.

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

--
KH_G
-----------------------------------------------------------------------
KH_GS's Profile: http://www.excelforum.com/member.php...fo&userid=3292
View this thread: http://www.excelforum.com/showthread.php?threadid=52740

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

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.



Greg Wilson Wrote:
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
------------------------------------------------------------------------
KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920
View this thread: http://www.excelforum.com/showthread...hreadid=527404

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.





Greg Wilson Wrote:
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




+-------------------------------------------------------------------+
|Filename: testscr.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4538 |
+-------------------------------------------------------------------+

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

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