any function to replace loop
Below loop works ok. But sheet ("pickuptime2") gets near over 15000
rows and it gets slower.Any suggestions would be helpful. Thank you very much in advance. Sub getpoint(tour As Range, tourdate As Range, hotel As Range) With Worksheets("pickuptime2") Set rng1 = .Range("A2", .Range("A" & Rows.count).End(xlUp)) For Each i In rng1 If tour.Value = i.Value And _ tourdate.Value = i.Offset(, 1).Value And _ hotel.Value = i.Offset(, 2) Then tour.Offset(, 12).Value = i.Offset(, 3) Exit Sub End If Next i MsgBox " No Value found " End With |
any function to replace loop
Function getpoint(Tour As Range, TourDate As Range, Hotel As Range)
Dim i As Excel.Range Dim rng1 As Excel.Range Dim varAllValues As Variant varAllValues = Tour.Value & TourDate.Value & Hotel.Value With Worksheets("pickuptime2") Set rng1 = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With For Each i In rng1 If varAllValues = i.Value & i.Offset(, 1).Value & i.Offset(, 2) Then Tour.Offset(, 12).Value = i.Offset(, 3) Exit Function End If Next i MsgBox " No Value found " End Function '-- Sub FindThePoint() Call getpoint(Range("B5"), Range("B6"), Range("B7")) End Sub ------------- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware wrote in message Below loop works ok. But sheet ("pickuptime2") gets near over 15000 rows and it gets slower.Any suggestions would be helpful. Thank you very much in advance. Sub getpoint(tour As Range, tourdate As Range, hotel As Range) With Worksheets("pickuptime2") Set rng1 = .Range("A2", .Range("A" & Rows.count).End(xlUp)) For Each i In rng1 If tour.Value = i.Value And _ tourdate.Value = i.Offset(, 1).Value And _ hotel.Value = i.Offset(, 2) Then tour.Offset(, 12).Value = i.Offset(, 3) Exit Sub End If Next i MsgBox " No Value found " End With |
All times are GMT +1. The time now is 03:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com