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
|