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
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" |
#3
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" |
#4
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" |
#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" |
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 |