loop
 

Somewhere in column F i have the value "Production". It could be in
F100 or F157. It is not known.
My cursor is in cell G7. I am using activecell.offset(1,0).select to
scroll downwards. I want to move my cursor in column G only till the
row where the cell value in column F is "Production" what would be the
syntax?

something like While Not Cells(row, 6).Value = "Production"

loop
 

How about

Do While Not ActiveCell.Offset(0, -1).Value = "Production"
ActiveCell.Offset(1, 0).Select
Loop

" wrote:

Somewhere in column F i have the value "Production". It could be in
F100 or F157. It is not known.
My cursor is in cell G7. I am using activecell.offset(1,0).select to
scroll downwards. I want to move my cursor in column G only till the
row where the cell value in column F is "Production" what would be the
syntax?

something like While Not Cells(row, 6).Value = "Production"

loop
 

Sub Prod()
Dim MyRange As Range
Set MyRange = Range("G7:G100") ' adjust as needed
For Each cell In MyRange
If cell.Offset(0, -1).Value = "Production" Then
cell.Select
Exit Sub
End If
Next
End Sub

wrote:
Somewhere in column F i have the value "Production". It could be in
F100 or F157. It is not known.
My cursor is in cell G7. I am using activecell.offset(1,0).select to
scroll downwards. I want to move my cursor in column G only till the
row where the cell value in column F is "Production" what would be the
syntax?

something like While Not Cells(row, 6).Value = "Production"

loop
 

thanx

acampbell wrote:
Sub Prod()
Dim MyRange As Range
Set MyRange = Range("G7:G100") ' adjust as needed
For Each cell In MyRange
If cell.Offset(0, -1).Value = "Production" Then
cell.Select
Exit Sub
End If
Next
End Sub

wrote:
Somewhere in column F i have the value "Production". It could be in
F100 or F157. It is not known.
My cursor is in cell G7. I am using activecell.offset(1,0).select to
scroll downwards. I want to move my cursor in column G only till the
row where the cell value in column F is "Production" what would be the
syntax?

something like While Not Cells(row, 6).Value = "Production"

loop
 

Looping will be slower than FIND. Look in the vba help. If more than one
then use FINDNEXT.

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Somewhere in column F i have the value "Production". It could be in
F100 or F157. It is not known.
My cursor is in cell G7. I am using activecell.offset(1,0).select to
scroll downwards. I want to move my cursor in column G only till the
row where the cell value in column F is "Production" what would be the
syntax?

something like While Not Cells(row, 6).Value = "Production"

loop
 

Don,

Good point. Max, if not tested already, this will stop and exist on the
first occurence. If it is slow or you need to continue the search, post
back.

Alan

Don Guillett wrote:
Looping will be slower than FIND. Look in the vba help. If more than one
then use FINDNEXT.

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Somewhere in column F i have the value "Production". It could be in
F100 or F157. It is not known.
My cursor is in cell G7. I am using activecell.offset(1,0).select to
scroll downwards. I want to move my cursor in column G only till the
row where the cell value in column F is "Production" what would be the
syntax?

something like While Not Cells(row, 6).Value = "Production"

loop
 

Don,

Good point. This uses find instead.


Dim MyRange As Range
Dim MyFind
Set MyRange = Range("F7:F100") ' adjust as needed
Set MyFind = MyRange.Find(What:="Production", LookIn:=xlValues)
If Not MyFind Is Nothing Then
Range(MyFind.Address).Offset(0, 1).Select
Exit Sub
End If
End Sub



Don Guillett wrote:
Looping will be slower than FIND. Look in the vba help. If more than one
then use FINDNEXT.

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Somewhere in column F i have the value "Production". It could be in
F100 or F157. It is not known.
My cursor is in cell G7. I am using activecell.offset(1,0).select to
scroll downwards. I want to move my cursor in column G only till the
row where the cell value in column F is "Production" what would be the
syntax?

something like While Not Cells(row, 6).Value = "Production"

loop
 

or. But,why select???

Sub findit1()
'On Error Resume Next 'uncomment if you don't want error if not found
Cells(Range("f7:f100").Find("Production").Row + 1, "F").Select
End Sub


--
Don Guillett
SalesAid Software

"acampbell" wrote in message
ups.com...
Don,

Good point. This uses find instead.


Dim MyRange As Range
Dim MyFind
Set MyRange = Range("F7:F100") ' adjust as needed
Set MyFind = MyRange.Find(What:="Production", LookIn:=xlValues)
If Not MyFind Is Nothing Then
Range(MyFind.Address).Offset(0, 1).Select
Exit Sub
End If
End Sub



Don Guillett wrote:
Looping will be slower than FIND. Look in the vba help. If more than one
then use FINDNEXT.

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Somewhere in column F i have the value "Production". It could be in
F100 or F157. It is not known.
My cursor is in cell G7. I am using activecell.offset(1,0).select to
scroll downwards. I want to move my cursor in column G only till the
row where the cell value in column F is "Production" what would be the
syntax?

something like While Not Cells(row, 6).Value = "Production"

loop
 

Another good point. I am much more a student than a teacher out here.
Any advice on more efficient/effective coding is always welcome.

Thanks.

Alan

Don Guillett wrote:
or. But,why select???

Sub findit1()
'On Error Resume Next 'uncomment if you don't want error if not found
Cells(Range("f7:f100").Find("Production").Row + 1, "F").Select
End Sub


--
Don Guillett
SalesAid Software

"acampbell" wrote in message
ups.com...
Don,

Good point. This uses find instead.


Dim MyRange As Range
Dim MyFind
Set MyRange = Range("F7:F100") ' adjust as needed
Set MyFind = MyRange.Find(What:="Production", LookIn:=xlValues)
If Not MyFind Is Nothing Then
Range(MyFind.Address).Offset(0, 1).Select
Exit Sub
End If
End Sub



Don Guillett wrote:
Looping will be slower than FIND. Look in the vba help. If more than one
then use FINDNEXT.

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Somewhere in column F i have the value "Production". It could be in
F100 or F157. It is not known.
My cursor is in cell G7. I am using activecell.offset(1,0).select to
scroll downwards. I want to move my cursor in column G only till the
row where the cell value in column F is "Production" what would be the
syntax?

something like While Not Cells(row, 6).Value = "Production"

loop
 

Any advice on more efficient/effective coding is always welcome.

Directly trying to use the results of a method that could result in an error
as done in the example would be a poor approach. Even if you are sure the
sought value is present, failure to account for persistent values of
arguments to that method could again result in an error or inconsistent
results.

--
Regards,
Tom Ogilvy


"acampbell" wrote in message
oups.com...
Another good point. I am much more a student than a teacher out here.
Any advice on more efficient/effective coding is always welcome.

Thanks.

Alan

Don Guillett wrote:
or. But,why select???

Sub findit1()
'On Error Resume Next 'uncomment if you don't want error if not found
Cells(Range("f7:f100").Find("Production").Row + 1, "F").Select
End Sub


--
Don Guillett
SalesAid Software

"acampbell" wrote in message
ups.com...
Don,

Good point. This uses find instead.


Dim MyRange As Range
Dim MyFind
Set MyRange = Range("F7:F100") ' adjust as needed
Set MyFind = MyRange.Find(What:="Production", LookIn:=xlValues)
If Not MyFind Is Nothing Then
Range(MyFind.Address).Offset(0, 1).Select
Exit Sub
End If
End Sub



Don Guillett wrote:
Looping will be slower than FIND. Look in the vba help. If more than
one
then use FINDNEXT.

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Somewhere in column F i have the value "Production". It could be in
F100 or F157. It is not known.
My cursor is in cell G7. I am using activecell.offset(1,0).select to
scroll downwards. I want to move my cursor in column G only till the
row where the cell value in column F is "Production" what would be
the
syntax?

something like While Not Cells(row, 6).Value = "Production"

loop
 

Tom,

I read your response and looked in to other posts in which "persistent
values" may have contributed to errors in results or routines. Below
is a snippet of a response you provided in another thread. Would it be
good practice to employ this suggestion in any find operation?

....After looking at your code, I would suggest you provide all
arguments to the find function. Several of the arguments have
persistent
values and could be causing the find operation to fail mysteriously...

Thanks.

Alan




Tom Ogilvy wrote:
Any advice on more efficient/effective coding is always welcome.

Directly trying to use the results of a method that could result in an error
as done in the example would be a poor approach. Even if you are sure the
sought value is present, failure to account for persistent values of
arguments to that method could again result in an error or inconsistent
results.

--
Regards,
Tom Ogilvy


"acampbell" wrote in message
oups.com...
Another good point. I am much more a student than a teacher out here.
Any advice on more efficient/effective coding is always welcome.

Thanks.

Alan

Don Guillett wrote:
or. But,why select???

Sub findit1()
'On Error Resume Next 'uncomment if you don't want error if not found
Cells(Range("f7:f100").Find("Production").Row + 1, "F").Select
End Sub


--
Don Guillett
SalesAid Software

"acampbell" wrote in message
ups.com...
Don,

Good point. This uses find instead.


Dim MyRange As Range
Dim MyFind
Set MyRange = Range("F7:F100") ' adjust as needed
Set MyFind = MyRange.Find(What:="Production", LookIn:=xlValues)
If Not MyFind Is Nothing Then
Range(MyFind.Address).Offset(0, 1).Select
Exit Sub
End If
End Sub



Don Guillett wrote:
Looping will be slower than FIND. Look in the vba help. If more than
one
then use FINDNEXT.

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Somewhere in column F i have the value "Production". It could be in
F100 or F157. It is not known.
My cursor is in cell G7. I am using activecell.offset(1,0).select to
scroll downwards. I want to move my cursor in column G only till the
row where the cell value in column F is "Production" what would be
the
syntax?

something like While Not Cells(row, 6).Value = "Production"

loop
 

easy to find in the vba help index for FIND or FINDNEXT

--
Don Guillett
SalesAid Software

"acampbell" wrote in message
ups.com...
Tom,

I read your response and looked in to other posts in which "persistent
values" may have contributed to errors in results or routines. Below
is a snippet of a response you provided in another thread. Would it be
good practice to employ this suggestion in any find operation?

...After looking at your code, I would suggest you provide all
arguments to the find function. Several of the arguments have
persistent
values and could be causing the find operation to fail mysteriously...

Thanks.

Alan




Tom Ogilvy wrote:
Any advice on more efficient/effective coding is always welcome.

Directly trying to use the results of a method that could result in an
error
as done in the example would be a poor approach. Even if you are sure
the
sought value is present, failure to account for persistent values of
arguments to that method could again result in an error or inconsistent
results.

--
Regards,
Tom Ogilvy


"acampbell" wrote in message
oups.com...
Another good point. I am much more a student than a teacher out here.
Any advice on more efficient/effective coding is always welcome.

Thanks.

Alan

Don Guillett wrote:
or. But,why select???

Sub findit1()
'On Error Resume Next 'uncomment if you don't want error if not found
Cells(Range("f7:f100").Find("Production").Row + 1, "F").Select
End Sub


--
Don Guillett
SalesAid Software

"acampbell" wrote in message
ups.com...
Don,

Good point. This uses find instead.


Dim MyRange As Range
Dim MyFind
Set MyRange = Range("F7:F100") ' adjust as needed
Set MyFind = MyRange.Find(What:="Production", LookIn:=xlValues)
If Not MyFind Is Nothing Then
Range(MyFind.Address).Offset(0, 1).Select
Exit Sub
End If
End Sub



Don Guillett wrote:
Looping will be slower than FIND. Look in the vba help. If more
than
one
then use FINDNEXT.

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Somewhere in column F i have the value "Production". It could be
in
F100 or F157. It is not known.
My cursor is in cell G7. I am using activecell.offset(1,0).select
to
scroll downwards. I want to move my cursor in column G only till
the
row where the cell value in column F is "Production" what would
be
the
syntax?

something like While Not Cells(row, 6).Value = "Production"

loop
 

Don,

Thanks. As many times as I have referred to help on this topic, I have
overlooked this one critical sentence in the remarks (...To avoid
problems, set these arguments explicitly each time you use this
method.)

Alan

Don Guillett wrote:
easy to find in the vba help index for FIND or FINDNEXT

--
Don Guillett
SalesAid Software

"acampbell" wrote in message
ups.com...
Tom,

I read your response and looked in to other posts in which "persistent
values" may have contributed to errors in results or routines. Below
is a snippet of a response you provided in another thread. Would it be
good practice to employ this suggestion in any find operation?

...After looking at your code, I would suggest you provide all
arguments to the find function. Several of the arguments have
persistent
values and could be causing the find operation to fail mysteriously...

Thanks.

Alan




Tom Ogilvy wrote:
Any advice on more efficient/effective coding is always welcome.

Directly trying to use the results of a method that could result in an
error
as done in the example would be a poor approach. Even if you are sure
the
sought value is present, failure to account for persistent values of
arguments to that method could again result in an error or inconsistent
results.

--
Regards,
Tom Ogilvy


"acampbell" wrote in message
oups.com...
Another good point. I am much more a student than a teacher out here.
Any advice on more efficient/effective coding is always welcome.

Thanks.

Alan

Don Guillett wrote:
or. But,why select???

Sub findit1()
'On Error Resume Next 'uncomment if you don't want error if not found
Cells(Range("f7:f100").Find("Production").Row + 1, "F").Select
End Sub


--
Don Guillett
SalesAid Software

"acampbell" wrote in message
ups.com...
Don,

Good point. This uses find instead.


Dim MyRange As Range
Dim MyFind
Set MyRange = Range("F7:F100") ' adjust as needed
Set MyFind = MyRange.Find(What:="Production", LookIn:=xlValues)
If Not MyFind Is Nothing Then
Range(MyFind.Address).Offset(0, 1).Select
Exit Sub
End If
End Sub



Don Guillett wrote:
Looping will be slower than FIND. Look in the vba help. If more
than
one
then use FINDNEXT.

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Somewhere in column F i have the value "Production". It could be
in
F100 or F157. It is not known.
My cursor is in cell G7. I am using activecell.offset(1,0).select
to
scroll downwards. I want to move my cursor in column G only till
the
row where the cell value in column F is "Production" what would
be
the
syntax?

something like While Not Cells(row, 6).Value = "Production"