Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range offset
Can anyone tell me how to set a range offset? i am trying to write a
script that will start from the active cell and perform an action 1 row above and several columns to the left. example: if cell "N100" is the active cell my script will go up to "N99" and over to column "D" then perform its action. God bless jsd219 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range offset
ActiveCell.Offset(-1, -10).Select
Regards, Paul "jsd219" wrote in message oups.com... Can anyone tell me how to set a range offset? i am trying to write a script that will start from the active cell and perform an action 1 row above and several columns to the left. example: if cell "N100" is the active cell my script will go up to "N99" and over to column "D" then perform its action. God bless jsd219 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range offset
ok, what am i doing wrong here?
Set rng = ActiveCell.Offset(-1, -10).Select i get an error God bless jsd219 PCLIVE remove this wrote: ActiveCell.Offset(-1, -10).Select Regards, Paul "jsd219" wrote in message oups.com... Can anyone tell me how to set a range offset? i am trying to write a script that will start from the active cell and perform an action 1 row above and several columns to the left. example: if cell "N100" is the active cell my script will go up to "N99" and over to column "D" then perform its action. God bless jsd219 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range offset
you don't use select at the end
Set rng = ActiveCell.Offset(-1, -10) rng.Select but you rarely need to select, you can just act on the rng -- Gary "jsd219" wrote in message ups.com... ok, what am i doing wrong here? Set rng = ActiveCell.Offset(-1, -10).Select i get an error God bless jsd219 PCLIVE remove this wrote: ActiveCell.Offset(-1, -10).Select Regards, Paul "jsd219" wrote in message oups.com... Can anyone tell me how to set a range offset? i am trying to write a script that will start from the active cell and perform an action 1 row above and several columns to the left. example: if cell "N100" is the active cell my script will go up to "N99" and over to column "D" then perform its action. God bless jsd219 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range offset
Set rng = ActiveCell(0, -9)
Alan Beban jsd219 wrote: Can anyone tell me how to set a range offset? i am trying to write a script that will start from the active cell and perform an action 1 row above and several columns to the left. example: if cell "N100" is the active cell my script will go up to "N99" and over to column "D" then perform its action. God bless jsd219 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range offset
Here is a problem i am having, i need to not only make the range start
one row above the selected cell but i also need it to cover multiple columns. any ideas? God bless jsd219 Alan Beban wrote: Set rng = ActiveCell(0, -9) Alan Beban jsd219 wrote: Can anyone tell me how to set a range offset? i am trying to write a script that will start from the active cell and perform an action 1 row above and several columns to the left. example: if cell "N100" is the active cell my script will go up to "N99" and over to column "D" then perform its action. God bless jsd219 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range offset
give us an example of exactly what you're trying to do
-- Gary "jsd219" wrote in message ups.com... Here is a problem i am having, i need to not only make the range start one row above the selected cell but i also need it to cover multiple columns. any ideas? God bless jsd219 Alan Beban wrote: Set rng = ActiveCell(0, -9) Alan Beban jsd219 wrote: Can anyone tell me how to set a range offset? i am trying to write a script that will start from the active cell and perform an action 1 row above and several columns to the left. example: if cell "N100" is the active cell my script will go up to "N99" and over to column "D" then perform its action. God bless jsd219 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range offset
Sure this is a post from an earlier thread:
I am trying to write a script that looks for a cell with specified text in a specified column, once it finds the cell it needs to check six different columns (a,b,c,d,e,f) one row above and find the column that has an "x", it then needs to place an "x" in its row one column to the right from the previous "x" example: if it finds the cell with "Went up the hill" the script needs to look up one row and check columns (a,b,c,d,e,f) until it finds the "X". In this case it will find the "X" on the row with "JACK AND JILL" in column "a" so it will place an "X" for the row "Went up the hill" (the orginal cell searched for) in column "b" a b c d e f g X JACK AND JILL Went up the hill To fetch 3.5 Pales God bless jsd219 Gary Keramidas wrote: give us an example of exactly what you're trying to do -- Gary "jsd219" wrote in message ups.com... Here is a problem i am having, i need to not only make the range start one row above the selected cell but i also need it to cover multiple columns. any ideas? God bless jsd219 Alan Beban wrote: Set rng = ActiveCell(0, -9) Alan Beban jsd219 wrote: Can anyone tell me how to set a range offset? i am trying to write a script that will start from the active cell and perform an action 1 row above and several columns to the left. example: if cell "N100" is the active cell my script will go up to "N99" and over to column "D" then perform its action. God bless jsd219 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range offset
Dim rng as Range, rng1 as Range
Dim res as variant set rng = cells.Find("Went up the hill") set rng1 = cells(rng.row-1,"A").resize(1,6) res = application.Match("x",rng1,0) if not iserror(res) then cells(rng.row,res + 1).Value = "x" end if What happends if the match was made in F; write to G? What happended to Don? -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... Sure this is a post from an earlier thread: I am trying to write a script that looks for a cell with specified text in a specified column, once it finds the cell it needs to check six different columns (a,b,c,d,e,f) one row above and find the column that has an "x", it then needs to place an "x" in its row one column to the right from the previous "x" example: if it finds the cell with "Went up the hill" the script needs to look up one row and check columns (a,b,c,d,e,f) until it finds the "X". In this case it will find the "X" on the row with "JACK AND JILL" in column "a" so it will place an "X" for the row "Went up the hill" (the orginal cell searched for) in column "b" a b c d e f g X JACK AND JILL Went up the hill To fetch 3.5 Pales God bless jsd219 Gary Keramidas wrote: give us an example of exactly what you're trying to do -- Gary "jsd219" wrote in message ups.com... Here is a problem i am having, i need to not only make the range start one row above the selected cell but i also need it to cover multiple columns. any ideas? God bless jsd219 Alan Beban wrote: Set rng = ActiveCell(0, -9) Alan Beban jsd219 wrote: Can anyone tell me how to set a range offset? i am trying to write a script that will start from the active cell and perform an action 1 row above and several columns to the left. example: if cell "N100" is the active cell my script will go up to "N99" and over to column "D" then perform its action. God bless jsd219 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range offset
If the match is made in F it should just place an "x" in the same
column (F) but i was only planning on using this for a small segment of the script. I am trying to figure this part out for two reasons, to keep moving forward tonight and to learn how to do this. :-) The plan i have for this script is to be called by another script that searches the cells for specific criteria i.e All CAPS or Starts with a number, etc. once it finds the proper cell then it will, hopefully, call up this script to place an "x" in the appropriate place. The hardest task so far is figuring out the rules to search for, for instance: STANDARDS FOR FOREIGN LANGUAGE LEARNING Vocabulario en acción 1 Communication 1.1 Students engage in conversations i can easily find All CAPS and "Word Space NumberDotumber but the middle cell is the hardest. I have started to conclude after all of this that i might have to have the script start at a starting position and work its way down the column checking each cell not only for a series of rules but then comparing to the cell above and below. In other words, when the script comes to (in the above example) the middle cell (Vocab) it will have to check to see if it is all CAPS or has NumberDotNumber, if it does not it then it checks the cell above, (STANDARDS) if the cell above is All CAPS i now know that the cell (Vocab)'s "x" will fall in the tier one column to the right of whatever tier the cell above (STANDARDS) is. I have a few main rules that everything will fall under. Cell = Chapter # Cell contains = DAY or BLOCK First word or entire contents = All CAPS or the Text "Pre-AP Practice" First word = (# min.) i.e (3 min.) or (7 min) Cell contains = (Word Space NumberDotNumber) Any help with this would be awesome. i am way over my head. God bless jsd219 Tom Ogilvy wrote: Dim rng as Range, rng1 as Range Dim res as variant set rng = cells.Find("Went up the hill") set rng1 = cells(rng.row-1,"A").resize(1,6) res = application.Match("x",rng1,0) if not iserror(res) then cells(rng.row,res + 1).Value = "x" end if What happends if the match was made in F; write to G? What happended to Don? -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... Sure this is a post from an earlier thread: I am trying to write a script that looks for a cell with specified text in a specified column, once it finds the cell it needs to check six different columns (a,b,c,d,e,f) one row above and find the column that has an "x", it then needs to place an "x" in its row one column to the right from the previous "x" example: if it finds the cell with "Went up the hill" the script needs to look up one row and check columns (a,b,c,d,e,f) until it finds the "X". In this case it will find the "X" on the row with "JACK AND JILL" in column "a" so it will place an "X" for the row "Went up the hill" (the orginal cell searched for) in column "b" a b c d e f g X JACK AND JILL Went up the hill To fetch 3.5 Pales God bless jsd219 Gary Keramidas wrote: give us an example of exactly what you're trying to do -- Gary "jsd219" wrote in message ups.com... Here is a problem i am having, i need to not only make the range start one row above the selected cell but i also need it to cover multiple columns. any ideas? God bless jsd219 Alan Beban wrote: Set rng = ActiveCell(0, -9) Alan Beban jsd219 wrote: Can anyone tell me how to set a range offset? i am trying to write a script that will start from the active cell and perform an action 1 row above and several columns to the left. example: if cell "N100" is the active cell my script will go up to "N99" and over to column "D" then perform its action. God bless jsd219 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range offset
I got the script to work and i am using
Set rng = Selection now i am in the position you referenced earlier. Column "F" How would i tell the script that if it finds an "x" in column "F" of the above row place an "x" in the same column NOT one to the right. :-) God bless jsd219 Tom Ogilvy wrote: Dim rng as Range, rng1 as Range Dim res as variant set rng = cells.Find("Went up the hill") set rng1 = cells(rng.row-1,"A").resize(1,6) res = application.Match("x",rng1,0) if not iserror(res) then cells(rng.row,res + 1).Value = "x" end if What happends if the match was made in F; write to G? What happended to Don? -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... Sure this is a post from an earlier thread: I am trying to write a script that looks for a cell with specified text in a specified column, once it finds the cell it needs to check six different columns (a,b,c,d,e,f) one row above and find the column that has an "x", it then needs to place an "x" in its row one column to the right from the previous "x" example: if it finds the cell with "Went up the hill" the script needs to look up one row and check columns (a,b,c,d,e,f) until it finds the "X". In this case it will find the "X" on the row with "JACK AND JILL" in column "a" so it will place an "X" for the row "Went up the hill" (the orginal cell searched for) in column "b" a b c d e f g X JACK AND JILL Went up the hill To fetch 3.5 Pales God bless jsd219 Gary Keramidas wrote: give us an example of exactly what you're trying to do -- Gary "jsd219" wrote in message ups.com... Here is a problem i am having, i need to not only make the range start one row above the selected cell but i also need it to cover multiple columns. any ideas? God bless jsd219 Alan Beban wrote: Set rng = ActiveCell(0, -9) Alan Beban jsd219 wrote: Can anyone tell me how to set a range offset? i am trying to write a script that will start from the active cell and perform an action 1 row above and several columns to the left. example: if cell "N100" is the active cell my script will go up to "N99" and over to column "D" then perform its action. God bless jsd219 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range offset
If you mean in my code:
Dim rng as Range, rng1 as Range Dim res as variant set rng = selection set rng1 = cells(rng.row-1,"A").resize(1,6) res = application.Match("x",rng1,0) if not iserror(res) then if res < 6 then cells(rng.row,res + 1).Value = "x" else cells(rng.row,res).Value = "x" end if end if -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... I got the script to work and i am using Set rng = Selection now i am in the position you referenced earlier. Column "F" How would i tell the script that if it finds an "x" in column "F" of the above row place an "x" in the same column NOT one to the right. :-) God bless jsd219 Tom Ogilvy wrote: Dim rng as Range, rng1 as Range Dim res as variant set rng = cells.Find("Went up the hill") set rng1 = cells(rng.row-1,"A").resize(1,6) res = application.Match("x",rng1,0) if not iserror(res) then cells(rng.row,res + 1).Value = "x" end if What happends if the match was made in F; write to G? What happended to Don? -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... Sure this is a post from an earlier thread: I am trying to write a script that looks for a cell with specified text in a specified column, once it finds the cell it needs to check six different columns (a,b,c,d,e,f) one row above and find the column that has an "x", it then needs to place an "x" in its row one column to the right from the previous "x" example: if it finds the cell with "Went up the hill" the script needs to look up one row and check columns (a,b,c,d,e,f) until it finds the "X". In this case it will find the "X" on the row with "JACK AND JILL" in column "a" so it will place an "X" for the row "Went up the hill" (the orginal cell searched for) in column "b" a b c d e f g X JACK AND JILL Went up the hill To fetch 3.5 Pales God bless jsd219 Gary Keramidas wrote: give us an example of exactly what you're trying to do -- Gary "jsd219" wrote in message ups.com... Here is a problem i am having, i need to not only make the range start one row above the selected cell but i also need it to cover multiple columns. any ideas? God bless jsd219 Alan Beban wrote: Set rng = ActiveCell(0, -9) Alan Beban jsd219 wrote: Can anyone tell me how to set a range offset? i am trying to write a script that will start from the active cell and perform an action 1 row above and several columns to the left. example: if cell "N100" is the active cell my script will go up to "N99" and over to column "D" then perform its action. God bless jsd219 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range offset
Thank you Tom,
FYI: i had to change the lines to get the "x" in the proper place. Cells(rng.Row, res + 3).Value = "x" Else Cells(rng.Row, res + 2).Value = "x" I am only telling you this because when i changed another script you sent me you pointed out that the script would not work properly with my change and you should me what the correct change should be. :-) I just want to make sure the +3 and +2 above are ok. Speaking of the other script: For Each cell In rng start_str = InStr(1, cell.Value, myword, vbBinaryCompare) If start_str Then Range("B" & cell.Row).Value = 0 cell.EntireRow.Interior.Color = RGB(255, 255, 153) cell.Offset(0, 1).Value = Trim(Left(cell.Value, start_str - 1)) cell.Value = Trim(Right(cell.Value, Len(cell.Value) - start_str + 1)) End If This script works great when it looks for "Chapter" which is at the end of each cell. I have added below: for a second search that looks for "DAY" "DAY" is found at the beginning of the cells followed by a number. i have to keep DAY plus the number in the current cell and move the rest of its contents over. notice what i have done with the -5 and +2. can you show me what i did wrong? :-) start_str2 = InStr(1, cell.Value, myword2, vbBinaryCompare) If start_str2 Then cell.EntireRow.Interior.Color = RGB(255, 204, 0) Range("B" & cell.Row).Value = 1 cell.Offset(0, 1).Value = Trim(Right(cell.Value, Len(cell.Value) - start_str2 - 5)) cell.Value = Trim(Left(cell.Value, Len(myword2) + start_str2 + 2)) End If God bless jsd219 Tom Ogilvy wrote: If you mean in my code: Dim rng as Range, rng1 as Range Dim res as variant set rng = selection set rng1 = cells(rng.row-1,"A").resize(1,6) res = application.Match("x",rng1,0) if not iserror(res) then if res < 6 then cells(rng.row,res + 1).Value = "x" else cells(rng.row,res).Value = "x" end if end if -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... I got the script to work and i am using Set rng = Selection now i am in the position you referenced earlier. Column "F" How would i tell the script that if it finds an "x" in column "F" of the above row place an "x" in the same column NOT one to the right. :-) God bless jsd219 Tom Ogilvy wrote: Dim rng as Range, rng1 as Range Dim res as variant set rng = cells.Find("Went up the hill") set rng1 = cells(rng.row-1,"A").resize(1,6) res = application.Match("x",rng1,0) if not iserror(res) then cells(rng.row,res + 1).Value = "x" end if What happends if the match was made in F; write to G? What happended to Don? -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... Sure this is a post from an earlier thread: I am trying to write a script that looks for a cell with specified text in a specified column, once it finds the cell it needs to check six different columns (a,b,c,d,e,f) one row above and find the column that has an "x", it then needs to place an "x" in its row one column to the right from the previous "x" example: if it finds the cell with "Went up the hill" the script needs to look up one row and check columns (a,b,c,d,e,f) until it finds the "X". In this case it will find the "X" on the row with "JACK AND JILL" in column "a" so it will place an "X" for the row "Went up the hill" (the orginal cell searched for) in column "b" a b c d e f g X JACK AND JILL Went up the hill To fetch 3.5 Pales God bless jsd219 Gary Keramidas wrote: give us an example of exactly what you're trying to do -- Gary "jsd219" wrote in message ups.com... Here is a problem i am having, i need to not only make the range start one row above the selected cell but i also need it to cover multiple columns. any ideas? God bless jsd219 Alan Beban wrote: Set rng = ActiveCell(0, -9) Alan Beban jsd219 wrote: Can anyone tell me how to set a range offset? i am trying to write a script that will start from the active cell and perform an action 1 row above and several columns to the left. example: if cell "N100" is the active cell my script will go up to "N99" and over to column "D" then perform its action. God bless jsd219 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range offset
According to your description of what you wanted to do, it would be
incorrect. rng1 should refer to A:F of the row above the row you were working in. Application.Match("x",rng1,0) would return the column number where the x was located. If you want to put the new x in the column on to the right, then that would be Cells(rng.row,res + 1).Value = "x" res + 3 would be 3 columns to the right. res + 2 would be 2 columns to the right. demo'd from the immediate window: set rng1 = Range("A1:F1") Range("C1").Value = "x" res = Application.Match("x",rng1,0) ? res 3 -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... Thank you Tom, FYI: i had to change the lines to get the "x" in the proper place. Cells(rng.Row, res + 3).Value = "x" Else Cells(rng.Row, res + 2).Value = "x" I am only telling you this because when i changed another script you sent me you pointed out that the script would not work properly with my change and you should me what the correct change should be. :-) I just want to make sure the +3 and +2 above are ok. Speaking of the other script: For Each cell In rng start_str = InStr(1, cell.Value, myword, vbBinaryCompare) If start_str Then Range("B" & cell.Row).Value = 0 cell.EntireRow.Interior.Color = RGB(255, 255, 153) cell.Offset(0, 1).Value = Trim(Left(cell.Value, start_str - 1)) cell.Value = Trim(Right(cell.Value, Len(cell.Value) - start_str + 1)) End If This script works great when it looks for "Chapter" which is at the end of each cell. I have added below: for a second search that looks for "DAY" "DAY" is found at the beginning of the cells followed by a number. i have to keep DAY plus the number in the current cell and move the rest of its contents over. notice what i have done with the -5 and +2. can you show me what i did wrong? :-) start_str2 = InStr(1, cell.Value, myword2, vbBinaryCompare) If start_str2 Then cell.EntireRow.Interior.Color = RGB(255, 204, 0) Range("B" & cell.Row).Value = 1 cell.Offset(0, 1).Value = Trim(Right(cell.Value, Len(cell.Value) - start_str2 - 5)) cell.Value = Trim(Left(cell.Value, Len(myword2) + start_str2 + 2)) End If God bless jsd219 Tom Ogilvy wrote: If you mean in my code: Dim rng as Range, rng1 as Range Dim res as variant set rng = selection set rng1 = cells(rng.row-1,"A").resize(1,6) res = application.Match("x",rng1,0) if not iserror(res) then if res < 6 then cells(rng.row,res + 1).Value = "x" else cells(rng.row,res).Value = "x" end if end if -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... I got the script to work and i am using Set rng = Selection now i am in the position you referenced earlier. Column "F" How would i tell the script that if it finds an "x" in column "F" of the above row place an "x" in the same column NOT one to the right. :-) God bless jsd219 Tom Ogilvy wrote: Dim rng as Range, rng1 as Range Dim res as variant set rng = cells.Find("Went up the hill") set rng1 = cells(rng.row-1,"A").resize(1,6) res = application.Match("x",rng1,0) if not iserror(res) then cells(rng.row,res + 1).Value = "x" end if What happends if the match was made in F; write to G? What happended to Don? -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... Sure this is a post from an earlier thread: I am trying to write a script that looks for a cell with specified text in a specified column, once it finds the cell it needs to check six different columns (a,b,c,d,e,f) one row above and find the column that has an "x", it then needs to place an "x" in its row one column to the right from the previous "x" example: if it finds the cell with "Went up the hill" the script needs to look up one row and check columns (a,b,c,d,e,f) until it finds the "X". In this case it will find the "X" on the row with "JACK AND JILL" in column "a" so it will place an "X" for the row "Went up the hill" (the orginal cell searched for) in column "b" a b c d e f g X JACK AND JILL Went up the hill To fetch 3.5 Pales God bless jsd219 Gary Keramidas wrote: give us an example of exactly what you're trying to do -- Gary "jsd219" wrote in message ups.com... Here is a problem i am having, i need to not only make the range start one row above the selected cell but i also need it to cover multiple columns. any ideas? God bless jsd219 Alan Beban wrote: Set rng = ActiveCell(0, -9) Alan Beban jsd219 wrote: Can anyone tell me how to set a range offset? i am trying to write a script that will start from the active cell and perform an action 1 row above and several columns to the left. example: if cell "N100" is the active cell my script will go up to "N99" and over to column "D" then perform its action. God bless jsd219 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range offset
here is what i have: tha actual columns Start at column "C" and go to
cloumn "F" Set rng = Selection 'Cells.find ("Warm-Up") Set rng1 = Cells(rng.Row - 1, "C").Resize(1, 6) res = Application.Match("x", rng1, 0) If Not IsError(res) Then If res < 6 Then Cells(rng.Row, res + 3).Value = "x" Else Cells(rng.Row, res + 2).Value = "x" End If End If God bless jsd219 Tom Ogilvy wrote: According to your description of what you wanted to do, it would be incorrect. rng1 should refer to A:F of the row above the row you were working in. Application.Match("x",rng1,0) would return the column number where the x was located. If you want to put the new x in the column on to the right, then that would be Cells(rng.row,res + 1).Value = "x" res + 3 would be 3 columns to the right. res + 2 would be 2 columns to the right. demo'd from the immediate window: set rng1 = Range("A1:F1") Range("C1").Value = "x" res = Application.Match("x",rng1,0) ? res 3 -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... Thank you Tom, FYI: i had to change the lines to get the "x" in the proper place. Cells(rng.Row, res + 3).Value = "x" Else Cells(rng.Row, res + 2).Value = "x" I am only telling you this because when i changed another script you sent me you pointed out that the script would not work properly with my change and you should me what the correct change should be. :-) I just want to make sure the +3 and +2 above are ok. Speaking of the other script: For Each cell In rng start_str = InStr(1, cell.Value, myword, vbBinaryCompare) If start_str Then Range("B" & cell.Row).Value = 0 cell.EntireRow.Interior.Color = RGB(255, 255, 153) cell.Offset(0, 1).Value = Trim(Left(cell.Value, start_str - 1)) cell.Value = Trim(Right(cell.Value, Len(cell.Value) - start_str + 1)) End If This script works great when it looks for "Chapter" which is at the end of each cell. I have added below: for a second search that looks for "DAY" "DAY" is found at the beginning of the cells followed by a number. i have to keep DAY plus the number in the current cell and move the rest of its contents over. notice what i have done with the -5 and +2. can you show me what i did wrong? :-) start_str2 = InStr(1, cell.Value, myword2, vbBinaryCompare) If start_str2 Then cell.EntireRow.Interior.Color = RGB(255, 204, 0) Range("B" & cell.Row).Value = 1 cell.Offset(0, 1).Value = Trim(Right(cell.Value, Len(cell.Value) - start_str2 - 5)) cell.Value = Trim(Left(cell.Value, Len(myword2) + start_str2 + 2)) End If God bless jsd219 Tom Ogilvy wrote: If you mean in my code: Dim rng as Range, rng1 as Range Dim res as variant set rng = selection set rng1 = cells(rng.row-1,"A").resize(1,6) res = application.Match("x",rng1,0) if not iserror(res) then if res < 6 then cells(rng.row,res + 1).Value = "x" else cells(rng.row,res).Value = "x" end if end if -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... I got the script to work and i am using Set rng = Selection now i am in the position you referenced earlier. Column "F" How would i tell the script that if it finds an "x" in column "F" of the above row place an "x" in the same column NOT one to the right. :-) God bless jsd219 Tom Ogilvy wrote: Dim rng as Range, rng1 as Range Dim res as variant set rng = cells.Find("Went up the hill") set rng1 = cells(rng.row-1,"A").resize(1,6) res = application.Match("x",rng1,0) if not iserror(res) then cells(rng.row,res + 1).Value = "x" end if What happends if the match was made in F; write to G? What happended to Don? -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... Sure this is a post from an earlier thread: I am trying to write a script that looks for a cell with specified text in a specified column, once it finds the cell it needs to check six different columns (a,b,c,d,e,f) one row above and find the column that has an "x", it then needs to place an "x" in its row one column to the right from the previous "x" example: if it finds the cell with "Went up the hill" the script needs to look up one row and check columns (a,b,c,d,e,f) until it finds the "X". In this case it will find the "X" on the row with "JACK AND JILL" in column "a" so it will place an "X" for the row "Went up the hill" (the orginal cell searched for) in column "b" a b c d e f g X JACK AND JILL Went up the hill To fetch 3.5 Pales God bless jsd219 Gary Keramidas wrote: give us an example of exactly what you're trying to do -- Gary "jsd219" wrote in message ups.com... Here is a problem i am having, i need to not only make the range start one row above the selected cell but i also need it to cover multiple columns. any ideas? God bless jsd219 Alan Beban wrote: Set rng = ActiveCell(0, -9) Alan Beban jsd219 wrote: Can anyone tell me how to set a range offset? i am trying to write a script that will start from the active cell and perform an action 1 row above and several columns to the left. example: if cell "N100" is the active cell my script will go up to "N99" and over to column "D" then perform its action. God bless jsd219 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range offset
That certainly wasn't the situation originally described where you were
looking at A:F. Set rng = Selection 'Cells.find ("Warm-Up") Set rng1 = Cells(rng.Row - 1, "C").Resize(1, 4) res = Application.Match("x", rng1, 0) If Not IsError(res) Then If res < 4 Then Cells(rng.row, res + 3).Value = "x" Else Cells(rng.row, res + 2 ).Value = "x" ' or Cells(rng.row,6).Value = "x" End If End If -- Regards, Tom Ogilvy "jsd219" wrote in message oups.com... here is what i have: tha actual columns Start at column "C" and go to cloumn "F" Set rng = Selection 'Cells.find ("Warm-Up") Set rng1 = Cells(rng.Row - 1, "C").Resize(1, 6) res = Application.Match("x", rng1, 0) If Not IsError(res) Then If res < 6 Then Cells(rng.Row, res + 3).Value = "x" Else Cells(rng.Row, res + 2).Value = "x" End If End If God bless jsd219 Tom Ogilvy wrote: According to your description of what you wanted to do, it would be incorrect. rng1 should refer to A:F of the row above the row you were working in. Application.Match("x",rng1,0) would return the column number where the x was located. If you want to put the new x in the column on to the right, then that would be Cells(rng.row,res + 1).Value = "x" res + 3 would be 3 columns to the right. res + 2 would be 2 columns to the right. demo'd from the immediate window: set rng1 = Range("A1:F1") Range("C1").Value = "x" res = Application.Match("x",rng1,0) ? res 3 -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... Thank you Tom, FYI: i had to change the lines to get the "x" in the proper place. Cells(rng.Row, res + 3).Value = "x" Else Cells(rng.Row, res + 2).Value = "x" I am only telling you this because when i changed another script you sent me you pointed out that the script would not work properly with my change and you should me what the correct change should be. :-) I just want to make sure the +3 and +2 above are ok. Speaking of the other script: For Each cell In rng start_str = InStr(1, cell.Value, myword, vbBinaryCompare) If start_str Then Range("B" & cell.Row).Value = 0 cell.EntireRow.Interior.Color = RGB(255, 255, 153) cell.Offset(0, 1).Value = Trim(Left(cell.Value, start_str - 1)) cell.Value = Trim(Right(cell.Value, Len(cell.Value) - start_str + 1)) End If This script works great when it looks for "Chapter" which is at the end of each cell. I have added below: for a second search that looks for "DAY" "DAY" is found at the beginning of the cells followed by a number. i have to keep DAY plus the number in the current cell and move the rest of its contents over. notice what i have done with the -5 and +2. can you show me what i did wrong? :-) start_str2 = InStr(1, cell.Value, myword2, vbBinaryCompare) If start_str2 Then cell.EntireRow.Interior.Color = RGB(255, 204, 0) Range("B" & cell.Row).Value = 1 cell.Offset(0, 1).Value = Trim(Right(cell.Value, Len(cell.Value) - start_str2 - 5)) cell.Value = Trim(Left(cell.Value, Len(myword2) + start_str2 + 2)) End If God bless jsd219 Tom Ogilvy wrote: If you mean in my code: Dim rng as Range, rng1 as Range Dim res as variant set rng = selection set rng1 = cells(rng.row-1,"A").resize(1,6) res = application.Match("x",rng1,0) if not iserror(res) then if res < 6 then cells(rng.row,res + 1).Value = "x" else cells(rng.row,res).Value = "x" end if end if -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... I got the script to work and i am using Set rng = Selection now i am in the position you referenced earlier. Column "F" How would i tell the script that if it finds an "x" in column "F" of the above row place an "x" in the same column NOT one to the right. :-) God bless jsd219 Tom Ogilvy wrote: Dim rng as Range, rng1 as Range Dim res as variant set rng = cells.Find("Went up the hill") set rng1 = cells(rng.row-1,"A").resize(1,6) res = application.Match("x",rng1,0) if not iserror(res) then cells(rng.row,res + 1).Value = "x" end if What happends if the match was made in F; write to G? What happended to Don? -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... Sure this is a post from an earlier thread: I am trying to write a script that looks for a cell with specified text in a specified column, once it finds the cell it needs to check six different columns (a,b,c,d,e,f) one row above and find the column that has an "x", it then needs to place an "x" in its row one column to the right from the previous "x" example: if it finds the cell with "Went up the hill" the script needs to look up one row and check columns (a,b,c,d,e,f) until it finds the "X". In this case it will find the "X" on the row with "JACK AND JILL" in column "a" so it will place an "X" for the row "Went up the hill" (the orginal cell searched for) in column "b" a b c d e f g X JACK AND JILL Went up the hill To fetch 3.5 Pales God bless jsd219 Gary Keramidas wrote: give us an example of exactly what you're trying to do -- Gary "jsd219" wrote in message ups.com... Here is a problem i am having, i need to not only make the range start one row above the selected cell but i also need it to cover multiple columns. any ideas? God bless jsd219 Alan Beban wrote: Set rng = ActiveCell(0, -9) Alan Beban jsd219 wrote: Can anyone tell me how to set a range offset? i am trying to write a script that will start from the active cell and perform an action 1 row above and several columns to the left. example: if cell "N100" is the active cell my script will go up to "N99" and over to column "D" then perform its action. God bless jsd219 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range offset
My bad, i meant to say column "C" - "H"
God bless jsd219 Tom Ogilvy wrote: That certainly wasn't the situation originally described where you were looking at A:F. Set rng = Selection 'Cells.find ("Warm-Up") Set rng1 = Cells(rng.Row - 1, "C").Resize(1, 4) res = Application.Match("x", rng1, 0) If Not IsError(res) Then If res < 4 Then Cells(rng.row, res + 3).Value = "x" Else Cells(rng.row, res + 2 ).Value = "x" ' or Cells(rng.row,6).Value = "x" End If End If -- Regards, Tom Ogilvy "jsd219" wrote in message oups.com... here is what i have: tha actual columns Start at column "C" and go to cloumn "F" Set rng = Selection 'Cells.find ("Warm-Up") Set rng1 = Cells(rng.Row - 1, "C").Resize(1, 6) res = Application.Match("x", rng1, 0) If Not IsError(res) Then If res < 6 Then Cells(rng.Row, res + 3).Value = "x" Else Cells(rng.Row, res + 2).Value = "x" End If End If God bless jsd219 Tom Ogilvy wrote: According to your description of what you wanted to do, it would be incorrect. rng1 should refer to A:F of the row above the row you were working in. Application.Match("x",rng1,0) would return the column number where the x was located. If you want to put the new x in the column on to the right, then that would be Cells(rng.row,res + 1).Value = "x" res + 3 would be 3 columns to the right. res + 2 would be 2 columns to the right. demo'd from the immediate window: set rng1 = Range("A1:F1") Range("C1").Value = "x" res = Application.Match("x",rng1,0) ? res 3 -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... Thank you Tom, FYI: i had to change the lines to get the "x" in the proper place. Cells(rng.Row, res + 3).Value = "x" Else Cells(rng.Row, res + 2).Value = "x" I am only telling you this because when i changed another script you sent me you pointed out that the script would not work properly with my change and you should me what the correct change should be. :-) I just want to make sure the +3 and +2 above are ok. Speaking of the other script: For Each cell In rng start_str = InStr(1, cell.Value, myword, vbBinaryCompare) If start_str Then Range("B" & cell.Row).Value = 0 cell.EntireRow.Interior.Color = RGB(255, 255, 153) cell.Offset(0, 1).Value = Trim(Left(cell.Value, start_str - 1)) cell.Value = Trim(Right(cell.Value, Len(cell.Value) - start_str + 1)) End If This script works great when it looks for "Chapter" which is at the end of each cell. I have added below: for a second search that looks for "DAY" "DAY" is found at the beginning of the cells followed by a number. i have to keep DAY plus the number in the current cell and move the rest of its contents over. notice what i have done with the -5 and +2. can you show me what i did wrong? :-) start_str2 = InStr(1, cell.Value, myword2, vbBinaryCompare) If start_str2 Then cell.EntireRow.Interior.Color = RGB(255, 204, 0) Range("B" & cell.Row).Value = 1 cell.Offset(0, 1).Value = Trim(Right(cell.Value, Len(cell.Value) - start_str2 - 5)) cell.Value = Trim(Left(cell.Value, Len(myword2) + start_str2 + 2)) End If God bless jsd219 Tom Ogilvy wrote: If you mean in my code: Dim rng as Range, rng1 as Range Dim res as variant set rng = selection set rng1 = cells(rng.row-1,"A").resize(1,6) res = application.Match("x",rng1,0) if not iserror(res) then if res < 6 then cells(rng.row,res + 1).Value = "x" else cells(rng.row,res).Value = "x" end if end if -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... I got the script to work and i am using Set rng = Selection now i am in the position you referenced earlier. Column "F" How would i tell the script that if it finds an "x" in column "F" of the above row place an "x" in the same column NOT one to the right. :-) God bless jsd219 Tom Ogilvy wrote: Dim rng as Range, rng1 as Range Dim res as variant set rng = cells.Find("Went up the hill") set rng1 = cells(rng.row-1,"A").resize(1,6) res = application.Match("x",rng1,0) if not iserror(res) then cells(rng.row,res + 1).Value = "x" end if What happends if the match was made in F; write to G? What happended to Don? -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... Sure this is a post from an earlier thread: I am trying to write a script that looks for a cell with specified text in a specified column, once it finds the cell it needs to check six different columns (a,b,c,d,e,f) one row above and find the column that has an "x", it then needs to place an "x" in its row one column to the right from the previous "x" example: if it finds the cell with "Went up the hill" the script needs to look up one row and check columns (a,b,c,d,e,f) until it finds the "X". In this case it will find the "X" on the row with "JACK AND JILL" in column "a" so it will place an "X" for the row "Went up the hill" (the orginal cell searched for) in column "b" a b c d e f g X JACK AND JILL Went up the hill To fetch 3.5 Pales God bless jsd219 Gary Keramidas wrote: give us an example of exactly what you're trying to do -- Gary "jsd219" wrote in message ups.com... Here is a problem i am having, i need to not only make the range start one row above the selected cell but i also need it to cover multiple columns. any ideas? God bless jsd219 Alan Beban wrote: Set rng = ActiveCell(0, -9) Alan Beban jsd219 wrote: Can anyone tell me how to set a range offset? i am trying to write a script that will start from the active cell and perform an action 1 row above and several columns to the left. example: if cell "N100" is the active cell my script will go up to "N99" and over to column "D" then perform its action. God bless jsd219 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range offset
Which time?
here is what i have: tha actual columns Start at column "C" and go to cloumn "F" it needs to check six different columns (a,b,c,d,e,f) one row above If you actually want C - H the code would be: Set rng = Selection 'Cells.find ("Warm-Up") Set rng1 = Cells(rng.Row - 1, "C").Resize(1, 6) res = Application.Match("x", rng1, 0) If Not IsError(res) Then If res < 6 Then Cells(rng.row, res + 3).Value = "x" Else ' found in column H, so write in H Cells(rng.row, res + 2 ).Value = "x" ' or Cells(rng.row,8).Value = "x" End If End If -- Regards, Tom Ogilvy "jsd219" wrote in message oups.com... My bad, i meant to say column "C" - "H" God bless jsd219 Tom Ogilvy wrote: That certainly wasn't the situation originally described where you were looking at A:F. Set rng = Selection 'Cells.find ("Warm-Up") Set rng1 = Cells(rng.Row - 1, "C").Resize(1, 4) res = Application.Match("x", rng1, 0) If Not IsError(res) Then If res < 4 Then Cells(rng.row, res + 3).Value = "x" Else Cells(rng.row, res + 2 ).Value = "x" ' or Cells(rng.row,6).Value = "x" End If End If -- Regards, Tom Ogilvy "jsd219" wrote in message oups.com... here is what i have: tha actual columns Start at column "C" and go to cloumn "F" Set rng = Selection 'Cells.find ("Warm-Up") Set rng1 = Cells(rng.Row - 1, "C").Resize(1, 6) res = Application.Match("x", rng1, 0) If Not IsError(res) Then If res < 6 Then Cells(rng.Row, res + 3).Value = "x" Else Cells(rng.Row, res + 2).Value = "x" End If End If God bless jsd219 Tom Ogilvy wrote: According to your description of what you wanted to do, it would be incorrect. rng1 should refer to A:F of the row above the row you were working in. Application.Match("x",rng1,0) would return the column number where the x was located. If you want to put the new x in the column on to the right, then that would be Cells(rng.row,res + 1).Value = "x" res + 3 would be 3 columns to the right. res + 2 would be 2 columns to the right. demo'd from the immediate window: set rng1 = Range("A1:F1") Range("C1").Value = "x" res = Application.Match("x",rng1,0) ? res 3 -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... Thank you Tom, FYI: i had to change the lines to get the "x" in the proper place. Cells(rng.Row, res + 3).Value = "x" Else Cells(rng.Row, res + 2).Value = "x" I am only telling you this because when i changed another script you sent me you pointed out that the script would not work properly with my change and you should me what the correct change should be. :-) I just want to make sure the +3 and +2 above are ok. Speaking of the other script: For Each cell In rng start_str = InStr(1, cell.Value, myword, vbBinaryCompare) If start_str Then Range("B" & cell.Row).Value = 0 cell.EntireRow.Interior.Color = RGB(255, 255, 153) cell.Offset(0, 1).Value = Trim(Left(cell.Value, start_str - 1)) cell.Value = Trim(Right(cell.Value, Len(cell.Value) - start_str + 1)) End If This script works great when it looks for "Chapter" which is at the end of each cell. I have added below: for a second search that looks for "DAY" "DAY" is found at the beginning of the cells followed by a number. i have to keep DAY plus the number in the current cell and move the rest of its contents over. notice what i have done with the -5 and +2. can you show me what i did wrong? :-) start_str2 = InStr(1, cell.Value, myword2, vbBinaryCompare) If start_str2 Then cell.EntireRow.Interior.Color = RGB(255, 204, 0) Range("B" & cell.Row).Value = 1 cell.Offset(0, 1).Value = Trim(Right(cell.Value, Len(cell.Value) - start_str2 - 5)) cell.Value = Trim(Left(cell.Value, Len(myword2) + start_str2 + 2)) End If God bless jsd219 Tom Ogilvy wrote: If you mean in my code: Dim rng as Range, rng1 as Range Dim res as variant set rng = selection set rng1 = cells(rng.row-1,"A").resize(1,6) res = application.Match("x",rng1,0) if not iserror(res) then if res < 6 then cells(rng.row,res + 1).Value = "x" else cells(rng.row,res).Value = "x" end if end if -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... I got the script to work and i am using Set rng = Selection now i am in the position you referenced earlier. Column "F" How would i tell the script that if it finds an "x" in column "F" of the above row place an "x" in the same column NOT one to the right. :-) God bless jsd219 Tom Ogilvy wrote: Dim rng as Range, rng1 as Range Dim res as variant set rng = cells.Find("Went up the hill") set rng1 = cells(rng.row-1,"A").resize(1,6) res = application.Match("x",rng1,0) if not iserror(res) then cells(rng.row,res + 1).Value = "x" end if What happends if the match was made in F; write to G? What happended to Don? -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... Sure this is a post from an earlier thread: I am trying to write a script that looks for a cell with specified text in a specified column, once it finds the cell it needs to check six different columns (a,b,c,d,e,f) one row above and find the column that has an "x", it then needs to place an "x" in its row one column to the right from the previous "x" example: if it finds the cell with "Went up the hill" the script needs to look up one row and check columns (a,b,c,d,e,f) until it finds the "X". In this case it will find the "X" on the row with "JACK AND JILL" in column "a" so it will place an "X" for the row "Went up the hill" (the orginal cell searched for) in column "b" a b c d e f g X JACK AND JILL Went up the hill To fetch 3.5 Pales God bless jsd219 Gary Keramidas wrote: give us an example of exactly what you're trying to do -- Gary "jsd219" wrote in message ups.com... Here is a problem i am having, i need to not only make the range start one row above the selected cell but i also need it to cover multiple columns. any ideas? God bless jsd219 Alan Beban wrote: Set rng = ActiveCell(0, -9) Alan Beban jsd219 wrote: Can anyone tell me how to set a range offset? i am trying to write a script that will start from the active cell and perform an action 1 row above and several columns to the left. example: if cell "N100" is the active cell my script will go up to "N99" and over to column "D" then perform its action. God bless jsd219 |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range offset
start_str2 = InStr(1, cell.Value, myword2, vbBinaryCompare)
If start_str2 Then cell.EntireRow.Interior.Color = RGB(255, 204, 0) Range("B" & cell.Row).Value = 1 cell.Offset(0, 1).Value = Trim(Right(cell.Value, _ Len(cell.Value) - start_str2 - 5)) cell.Value = Trim(Left(cell.Value, start_str2 + 5)) End If worked for me. -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... Thank you Tom, FYI: i had to change the lines to get the "x" in the proper place. Cells(rng.Row, res + 3).Value = "x" Else Cells(rng.Row, res + 2).Value = "x" I am only telling you this because when i changed another script you sent me you pointed out that the script would not work properly with my change and you should me what the correct change should be. :-) I just want to make sure the +3 and +2 above are ok. Speaking of the other script: For Each cell In rng start_str = InStr(1, cell.Value, myword, vbBinaryCompare) If start_str Then Range("B" & cell.Row).Value = 0 cell.EntireRow.Interior.Color = RGB(255, 255, 153) cell.Offset(0, 1).Value = Trim(Left(cell.Value, start_str - 1)) cell.Value = Trim(Right(cell.Value, Len(cell.Value) - start_str + 1)) End If This script works great when it looks for "Chapter" which is at the end of each cell. I have added below: for a second search that looks for "DAY" "DAY" is found at the beginning of the cells followed by a number. i have to keep DAY plus the number in the current cell and move the rest of its contents over. notice what i have done with the -5 and +2. can you show me what i did wrong? :-) start_str2 = InStr(1, cell.Value, myword2, vbBinaryCompare) If start_str2 Then cell.EntireRow.Interior.Color = RGB(255, 204, 0) Range("B" & cell.Row).Value = 1 cell.Offset(0, 1).Value = Trim(Right(cell.Value, Len(cell.Value) - start_str2 - 5)) cell.Value = Trim(Left(cell.Value, Len(myword2) + start_str2 + 2)) End If God bless jsd219 Tom Ogilvy wrote: If you mean in my code: Dim rng as Range, rng1 as Range Dim res as variant set rng = selection set rng1 = cells(rng.row-1,"A").resize(1,6) res = application.Match("x",rng1,0) if not iserror(res) then if res < 6 then cells(rng.row,res + 1).Value = "x" else cells(rng.row,res).Value = "x" end if end if -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... I got the script to work and i am using Set rng = Selection now i am in the position you referenced earlier. Column "F" How would i tell the script that if it finds an "x" in column "F" of the above row place an "x" in the same column NOT one to the right. :-) God bless jsd219 Tom Ogilvy wrote: Dim rng as Range, rng1 as Range Dim res as variant set rng = cells.Find("Went up the hill") set rng1 = cells(rng.row-1,"A").resize(1,6) res = application.Match("x",rng1,0) if not iserror(res) then cells(rng.row,res + 1).Value = "x" end if What happends if the match was made in F; write to G? What happended to Don? -- Regards, Tom Ogilvy "jsd219" wrote in message ups.com... Sure this is a post from an earlier thread: I am trying to write a script that looks for a cell with specified text in a specified column, once it finds the cell it needs to check six different columns (a,b,c,d,e,f) one row above and find the column that has an "x", it then needs to place an "x" in its row one column to the right from the previous "x" example: if it finds the cell with "Went up the hill" the script needs to look up one row and check columns (a,b,c,d,e,f) until it finds the "X". In this case it will find the "X" on the row with "JACK AND JILL" in column "a" so it will place an "X" for the row "Went up the hill" (the orginal cell searched for) in column "b" a b c d e f g X JACK AND JILL Went up the hill To fetch 3.5 Pales God bless jsd219 Gary Keramidas wrote: give us an example of exactly what you're trying to do -- Gary "jsd219" wrote in message ups.com... Here is a problem i am having, i need to not only make the range start one row above the selected cell but i also need it to cover multiple columns. any ideas? God bless jsd219 Alan Beban wrote: Set rng = ActiveCell(0, -9) Alan Beban jsd219 wrote: Can anyone tell me how to set a range offset? i am trying to write a script that will start from the active cell and perform an action 1 row above and several columns to the left. example: if cell "N100" is the active cell my script will go up to "N99" and over to column "D" then perform its action. God bless jsd219 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OFFSET Range | Excel Worksheet Functions | |||
Copy range using offset range value | Excel Programming | |||
Problem with Range.Cells.Offset and Range.Cells( row + offset, column) | Excel Programming | |||
range offset | Excel Programming | |||
Offset Range | Excel Programming |