Relative columns in range vs absolute columns
 

In the snippet below I am searching for a cell in a range that has only
one character. If there is a better way to do this I would appreciate
the advice. However, my real question has to do with relative column and
row numbers within a range.

As you can see my range starts at column R ends at column AH and is 16
rows deep. When I use temp.row I get 1, which is fine as it is on the
first row but wouldn't be if I started my range at any row other than 1.
The string temp.column gives me 22. That is the 22nd column, but it is
only the 5th column of my range. Here is some comma delimited sample
data:

1lkjsd,1u34j,oiuqwd,isua,a,qoiwuer,ijaslkjdf

So, my search will find the character 'a', which I will then eliminate
from all other cells on that row and in the column as well. I wrote
functions that do this, but they take the range, the found string, the
realative row number and realative column number as arguments.

Dim Colb As Range
Dim temp As Range
Dim rowNumber As Integer
Dim columnNumber As Integer
Dim test1 As String, test2 As String, test3 As String
Dim strLength As Integer

Set Colb = Sheets("Puzzle").Range("R1:AH16")

For Each temp In Colb
test2 = temp.Value
rowNumber = temp.row
columnNumber = temp.column
strLength = Len(test2)
If strLength = 1 Then
UpdateRow rowNumber, test2, Colb
UpdateColumn columnNumber, test2, Colb
End If
Next temp

Is there a different propoerty that will give me the relative column and
row number?

Thanks,

fybar

Relative columns in range vs absolute columns
 

Hello Fybar,

Here is some code that will give you Row and Column number information
about your Range...


Code:
--------------------

With Sheets("Puzzle").Range("R1:AH16")
FirstRow = .Row
FirstColumn = .Column
LastRow = .Rows.Count + FirstRow - 1
LastColumn = .Columns.Count + FirstColumn - 1
End With

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=481811

Relative columns in range vs absolute columns
 

Not without doing the math

For Each temp In Colb
test2 = temp.Value
rowNumber = temp.row - colb.row + 1
columnNumber = temp.column - colb.column + 1
strLength = Len(test2)
If strLength = 1 Then
UpdateRow rowNumber, test2, Colb
UpdateColumn columnNumber, test2, Colb
End If
Next temp

--
Regards,
Tom Ogilvy


"fybar" wrote in message
...
In the snippet below I am searching for a cell in a range that has only
one character. If there is a better way to do this I would appreciate
the advice. However, my real question has to do with relative column and
row numbers within a range.

As you can see my range starts at column R ends at column AH and is 16
rows deep. When I use temp.row I get 1, which is fine as it is on the
first row but wouldn't be if I started my range at any row other than 1.
The string temp.column gives me 22. That is the 22nd column, but it is
only the 5th column of my range. Here is some comma delimited sample
data:

1lkjsd,1u34j,oiuqwd,isua,a,qoiwuer,ijaslkjdf

So, my search will find the character 'a', which I will then eliminate
from all other cells on that row and in the column as well. I wrote
functions that do this, but they take the range, the found string, the
realative row number and realative column number as arguments.

Dim Colb As Range
Dim temp As Range
Dim rowNumber As Integer
Dim columnNumber As Integer
Dim test1 As String, test2 As String, test3 As String
Dim strLength As Integer

Set Colb = Sheets("Puzzle").Range("R1:AH16")

For Each temp In Colb
test2 = temp.Value
rowNumber = temp.row
columnNumber = temp.column
strLength = Len(test2)
If strLength = 1 Then
UpdateRow rowNumber, test2, Colb
UpdateColumn columnNumber, test2, Colb
End If
Next temp

Is there a different propoerty that will give me the relative column and
row number?

Thanks,

fybar