Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
For Each ... Next loop - need to reference the loop variable | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Loop Function unable to loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming |