Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop with array
The following code pops up my msg box... meaning it doesn't find what I am
looking for. Any ideas why? Thanks for the help. John Dim rngToSearch As Range Dim wks As Worksheet Dim rngFound As Range Dim DestCell As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set wks = Sheets("data") Set rngToSearch = Range("j7:j712") Set rngFound = rngToSearch.Find(what:=Array(Range("d1").Value, Range("d2").Value, Range("d3").Value, Range("d4").Value), LookIn:=xlValues, lookat:=xlWhole) If rngFound Is Nothing Then Range("a6").Select MsgBox "No new Floaters" Else Do With Worksheets("vlookup") Set DestCell = Range("a800").End(xlDown).Offset(1, 0) End With rngFound.EntireRow.Copy _ Destination:=DestCell Set rngFound = rngToSearch.FindNext Loop Until rngFound Is Nothing End If Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Sheets("data").Select Range("a6").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop with array
If it makes a difference, the values in column D and what I am trying to find
are dates... "John" wrote: The following code pops up my msg box... meaning it doesn't find what I am looking for. Any ideas why? Thanks for the help. John Dim rngToSearch As Range Dim wks As Worksheet Dim rngFound As Range Dim DestCell As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set wks = Sheets("data") Set rngToSearch = Range("j7:j712") Set rngFound = rngToSearch.Find(what:=Array(Range("d1").Value, Range("d2").Value, Range("d3").Value, Range("d4").Value), LookIn:=xlValues, lookat:=xlWhole) If rngFound Is Nothing Then Range("a6").Select MsgBox "No new Floaters" Else Do With Worksheets("vlookup") Set DestCell = Range("a800").End(xlDown).Offset(1, 0) End With rngFound.EntireRow.Copy _ Destination:=DestCell Set rngFound = rngToSearch.FindNext Loop Until rngFound Is Nothing End If Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Sheets("data").Select Range("a6").Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop with array
I changed the lookat:= to Lookat:= and it began to run... however it stays on
the same row, stuck in a continual loop and ideas on that? "John" wrote: If it makes a difference, the values in column D and what I am trying to find are dates... "John" wrote: The following code pops up my msg box... meaning it doesn't find what I am looking for. Any ideas why? Thanks for the help. John Dim rngToSearch As Range Dim wks As Worksheet Dim rngFound As Range Dim DestCell As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set wks = Sheets("data") Set rngToSearch = Range("j7:j712") Set rngFound = rngToSearch.Find(what:=Array(Range("d1").Value, Range("d2").Value, Range("d3").Value, Range("d4").Value), LookIn:=xlValues, lookat:=xlWhole) If rngFound Is Nothing Then Range("a6").Select MsgBox "No new Floaters" Else Do With Worksheets("vlookup") Set DestCell = Range("a800").End(xlDown).Offset(1, 0) End With rngFound.EntireRow.Copy _ Destination:=DestCell Set rngFound = rngToSearch.FindNext Loop Until rngFound Is Nothing End If Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Sheets("data").Select Range("a6").Select End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop with array
I didn't look too closely at your code, but this line jumped out at me:
Set rngFound = rngToSearch.Find(what:=Array(Range("d1").Value, Range("d2").Value, Range("d3").Value, Range("d4").Value), LookIn:=xlValues, lookat:=xlWhole) I would think you'd want to loop through those ranges and do your individual finds. dim myRng as range dim myCell as range dim FoundIt as boolean with activesheet set myrng = range("d1:d4") end with foundit = false for each mycell in myrng.cells Set rngFound = rngToSearch.Find(what:=mycell.Value), LookIn:=xlValues, lookat:=xlWhole) if rngfound is nothing then 'keep looking else foundit = true exit for end if next mycell if foundit = false then '.... John wrote: I changed the lookat:= to Lookat:= and it began to run... however it stays on the same row, stuck in a continual loop and ideas on that? "John" wrote: If it makes a difference, the values in column D and what I am trying to find are dates... "John" wrote: The following code pops up my msg box... meaning it doesn't find what I am looking for. Any ideas why? Thanks for the help. John Dim rngToSearch As Range Dim wks As Worksheet Dim rngFound As Range Dim DestCell As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set wks = Sheets("data") Set rngToSearch = Range("j7:j712") Set rngFound = rngToSearch.Find(what:=Array(Range("d1").Value, Range("d2").Value, Range("d3").Value, Range("d4").Value), LookIn:=xlValues, lookat:=xlWhole) If rngFound Is Nothing Then Range("a6").Select MsgBox "No new Floaters" Else Do With Worksheets("vlookup") Set DestCell = Range("a800").End(xlDown).Offset(1, 0) End With rngFound.EntireRow.Copy _ Destination:=DestCell Set rngFound = rngToSearch.FindNext Loop Until rngFound Is Nothing End If Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Sheets("data").Select Range("a6").Select End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop with array
Dave, thanks for the response. I reposted and I think you can get to the new
post here. http://www.microsoft.com/office/comm...5-68a316823a70 Would you mind taking a look at where we are at there? I would appreciate the help if you have the chance. Thanks "Dave Peterson" wrote: I didn't look too closely at your code, but this line jumped out at me: Set rngFound = rngToSearch.Find(what:=Array(Range("d1").Value, Range("d2").Value, Range("d3").Value, Range("d4").Value), LookIn:=xlValues, lookat:=xlWhole) I would think you'd want to loop through those ranges and do your individual finds. dim myRng as range dim myCell as range dim FoundIt as boolean with activesheet set myrng = range("d1:d4") end with foundit = false for each mycell in myrng.cells Set rngFound = rngToSearch.Find(what:=mycell.Value), LookIn:=xlValues, lookat:=xlWhole) if rngfound is nothing then 'keep looking else foundit = true exit for end if next mycell if foundit = false then '.... John wrote: I changed the lookat:= to Lookat:= and it began to run... however it stays on the same row, stuck in a continual loop and ideas on that? "John" wrote: If it makes a difference, the values in column D and what I am trying to find are dates... "John" wrote: The following code pops up my msg box... meaning it doesn't find what I am looking for. Any ideas why? Thanks for the help. John Dim rngToSearch As Range Dim wks As Worksheet Dim rngFound As Range Dim DestCell As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set wks = Sheets("data") Set rngToSearch = Range("j7:j712") Set rngFound = rngToSearch.Find(what:=Array(Range("d1").Value, Range("d2").Value, Range("d3").Value, Range("d4").Value), LookIn:=xlValues, lookat:=xlWhole) If rngFound Is Nothing Then Range("a6").Select MsgBox "No new Floaters" Else Do With Worksheets("vlookup") Set DestCell = Range("a800").End(xlDown).Offset(1, 0) End With rngFound.EntireRow.Copy _ Destination:=DestCell Set rngFound = rngToSearch.FindNext Loop Until rngFound Is Nothing End If Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Sheets("data").Select Range("a6").Select End Sub -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop with array
If Jim's code isn't working, I think you may want to take the time to post what
you want the code to do--in plain old words. And I'm sure Jim will be able to help once he understands your objective. John wrote: Dave, thanks for the response. I reposted and I think you can get to the new post here. http://www.microsoft.com/office/comm...5-68a316823a70 Would you mind taking a look at where we are at there? I would appreciate the help if you have the chance. Thanks "Dave Peterson" wrote: I didn't look too closely at your code, but this line jumped out at me: Set rngFound = rngToSearch.Find(what:=Array(Range("d1").Value, Range("d2").Value, Range("d3").Value, Range("d4").Value), LookIn:=xlValues, lookat:=xlWhole) I would think you'd want to loop through those ranges and do your individual finds. dim myRng as range dim myCell as range dim FoundIt as boolean with activesheet set myrng = range("d1:d4") end with foundit = false for each mycell in myrng.cells Set rngFound = rngToSearch.Find(what:=mycell.Value), LookIn:=xlValues, lookat:=xlWhole) if rngfound is nothing then 'keep looking else foundit = true exit for end if next mycell if foundit = false then '.... John wrote: I changed the lookat:= to Lookat:= and it began to run... however it stays on the same row, stuck in a continual loop and ideas on that? "John" wrote: If it makes a difference, the values in column D and what I am trying to find are dates... "John" wrote: The following code pops up my msg box... meaning it doesn't find what I am looking for. Any ideas why? Thanks for the help. John Dim rngToSearch As Range Dim wks As Worksheet Dim rngFound As Range Dim DestCell As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set wks = Sheets("data") Set rngToSearch = Range("j7:j712") Set rngFound = rngToSearch.Find(what:=Array(Range("d1").Value, Range("d2").Value, Range("d3").Value, Range("d4").Value), LookIn:=xlValues, lookat:=xlWhole) If rngFound Is Nothing Then Range("a6").Select MsgBox "No new Floaters" Else Do With Worksheets("vlookup") Set DestCell = Range("a800").End(xlDown).Offset(1, 0) End With rngFound.EntireRow.Copy _ Destination:=DestCell Set rngFound = rngToSearch.FindNext Loop Until rngFound Is Nothing End If Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Sheets("data").Select Range("a6").Select End Sub -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop with array
I am sure he will be able to as well, I just thought you may catch something
we are missing. Another perspective never hurt... and I believe he knows what I am trying to do quite well. "Dave Peterson" wrote: If Jim's code isn't working, I think you may want to take the time to post what you want the code to do--in plain old words. And I'm sure Jim will be able to help once he understands your objective. John wrote: Dave, thanks for the response. I reposted and I think you can get to the new post here. http://www.microsoft.com/office/comm...5-68a316823a70 Would you mind taking a look at where we are at there? I would appreciate the help if you have the chance. Thanks "Dave Peterson" wrote: I didn't look too closely at your code, but this line jumped out at me: Set rngFound = rngToSearch.Find(what:=Array(Range("d1").Value, Range("d2").Value, Range("d3").Value, Range("d4").Value), LookIn:=xlValues, lookat:=xlWhole) I would think you'd want to loop through those ranges and do your individual finds. dim myRng as range dim myCell as range dim FoundIt as boolean with activesheet set myrng = range("d1:d4") end with foundit = false for each mycell in myrng.cells Set rngFound = rngToSearch.Find(what:=mycell.Value), LookIn:=xlValues, lookat:=xlWhole) if rngfound is nothing then 'keep looking else foundit = true exit for end if next mycell if foundit = false then '.... John wrote: I changed the lookat:= to Lookat:= and it began to run... however it stays on the same row, stuck in a continual loop and ideas on that? "John" wrote: If it makes a difference, the values in column D and what I am trying to find are dates... "John" wrote: The following code pops up my msg box... meaning it doesn't find what I am looking for. Any ideas why? Thanks for the help. John Dim rngToSearch As Range Dim wks As Worksheet Dim rngFound As Range Dim DestCell As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set wks = Sheets("data") Set rngToSearch = Range("j7:j712") Set rngFound = rngToSearch.Find(what:=Array(Range("d1").Value, Range("d2").Value, Range("d3").Value, Range("d4").Value), LookIn:=xlValues, lookat:=xlWhole) If rngFound Is Nothing Then Range("a6").Select MsgBox "No new Floaters" Else Do With Worksheets("vlookup") Set DestCell = Range("a800").End(xlDown).Offset(1, 0) End With rngFound.EntireRow.Copy _ Destination:=DestCell Set rngFound = rngToSearch.FindNext Loop Until rngFound Is Nothing End If Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Sheets("data").Select Range("a6").Select End Sub -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Loop to create Array | Excel Programming | |||
Loop through array of worksheets | Excel Programming | |||
Help with Loop / Array / Ranges | Excel Programming | |||
Assign Results from If...Then and Loop to an Array (VBA) | Excel Programming | |||
Help -- Loop or Array? How to identify? | Excel Programming |