Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Only finds first Occurrence
Hello from Steved
Below works fine if I run it as a straight macro ie sub Test() Question when I ran it it would only do the first occurence City and put city in all the others please what is required for this to work. Thankyou. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Const sFIND As String = "TOTAL SHIFT HOURS" Dim vArr As Variant Dim rFound As Range Dim nCount As Long vArr = Array ("City", "Roskill", "Papakura", "Wiri", "Shore", "Orewa", " Swanson", "Panmure") nCount = 0 Set rFound = Cells.FIND( _ What:=sFIND, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) Do While Not rFound Is Nothing And nCount <= UBound (vArr) rFound.Value = vArr(nCount) nCount = nCount + 1 Set rFound = Cells.FindNext(after:=rFound) Loop End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Only finds first Occurrence
Everytime you make a change in the change event, you fire another change
event. I suspect Excel just quits after awhile, overcome by frustration. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Const sFIND As String = "TOTAL SHIFT HOURS" Dim vArr As Variant Dim rFound As Range Dim nCount As Long vArr = Array("City", "Roskill", "Papakura", _ "Wiri", "Shore", "Orewa", "Swanson", "Panmure") nCount = 0 Application.EnableEvents = False On Error goto ErrHandler Set rFound = Cells.FIND( _ What:=sFIND, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) Do While Not rFound Is Nothing _ And nCount <= UBound (vArr) rFound.Value = vArr(nCount) nCount = nCount + 1 Set rFound = Cells.FindNext(after:=rFound) Loop ErrHandler: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello from Steved Below works fine if I run it as a straight macro ie sub Test() Question when I ran it it would only do the first occurence City and put city in all the others please what is required for this to work. Thankyou. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Const sFIND As String = "TOTAL SHIFT HOURS" Dim vArr As Variant Dim rFound As Range Dim nCount As Long vArr = Array ("City", "Roskill", "Papakura", "Wiri", "Shore", "Orewa", " Swanson", "Panmure") nCount = 0 Set rFound = Cells.FIND( _ What:=sFIND, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) Do While Not rFound Is Nothing And nCount <= UBound (vArr) rFound.Value = vArr(nCount) nCount = nCount + 1 Set rFound = Cells.FindNext(after:=rFound) Loop End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Only finds first Occurrence
Hello Tom
Thanks very much. -----Original Message----- Everytime you make a change in the change event, you fire another change event. I suspect Excel just quits after awhile, overcome by frustration. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Const sFIND As String = "TOTAL SHIFT HOURS" Dim vArr As Variant Dim rFound As Range Dim nCount As Long vArr = Array("City", "Roskill", "Papakura", _ "Wiri", "Shore", "Orewa", "Swanson", "Panmure") nCount = 0 Application.EnableEvents = False On Error goto ErrHandler Set rFound = Cells.FIND( _ What:=sFIND, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) Do While Not rFound Is Nothing _ And nCount <= UBound (vArr) rFound.Value = vArr(nCount) nCount = nCount + 1 Set rFound = Cells.FindNext(after:=rFound) Loop ErrHandler: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello from Steved Below works fine if I run it as a straight macro ie sub Test() Question when I ran it it would only do the first occurence City and put city in all the others please what is required for this to work. Thankyou. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Const sFIND As String = "TOTAL SHIFT HOURS" Dim vArr As Variant Dim rFound As Range Dim nCount As Long vArr = Array ("City", "Roskill", "Papakura", "Wiri", "Shore", "Orewa", " Swanson", "Panmure") nCount = 0 Set rFound = Cells.FIND( _ What:=sFIND, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) Do While Not rFound Is Nothing And nCount <= UBound (vArr) rFound.Value = vArr(nCount) nCount = nCount + 1 Set rFound = Cells.FindNext(after:=rFound) Loop End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula the finds value of cell above | Excel Discussion (Misc queries) | |||
lookup only finds #n/a's | Excel Discussion (Misc queries) | |||
finds and concatenate | Excel Programming | |||
Multi-worksheet finds | Excel Programming | |||
Find finds nothing, and errors | Excel Programming |