Loop or condition? To check down the rows

  • Thread starter Thread starter KH_GS
  • Start date Start date
K

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 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
 
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
 
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 said:
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 said:
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?action=getinfo&userid=32920
View this thread: http://www.excelforum.com/showthread.php?threadid=527404
Click to expand...
Click to expand...
 
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 said:
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 said:
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?action=getinfo&userid=32920
View this thread: http://www.excelforum.com/showthread.php?threadid=527404
Click to expand...
Click to expand...


+-------------------------------------------------------------------+
|Filename: testscr.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4538 |
+-------------------------------------------------------------------+
 
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
 
Back
Top