Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Absolute References and Inserting columns | Excel Discussion (Misc queries) | |||
Range of cells: Convert relative reference into absolute | Excel Discussion (Misc queries) | |||
How to make columns absolute | Excel Discussion (Misc queries) | |||
name range from absolute to relative | Excel Worksheet Functions | |||
How do I change an Excel range of cells from relative to absolute. | Excel Worksheet Functions |