Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using the Find method in a VBA macro
I have got a working example of using the Find method in a VBA macro to get
the cell row of the value being searched for, but it only works if the value being searched for exists in the specified range. If the value being searched for is not in the range, then a runtime error occurs. My test contains a worksheet with the numbers 1 to 12 in cells A8 to A19. Here is my macro; Sub findRow() ' Worksheet contains the numbers 1 to 12 in cells A8 to A19. Dim lngMaxRow As Long Dim Row Range("A9").Select Selection.End(xlDown).Select lngMaxRow = ActiveCell.Row Range("A8:A" & lngMaxRow).Select 'Look for 4 Row = Selection.Find(what:="4", LookIn:=xlValues, LookAt:=xlWhole).Activate If Row < "" Then MsgBox "Found at row " & ActiveCell.Row End If 'Look for 15 Row = Selection.Find(what:="15", LookIn:=xlValues, LookAt:=xlWhole).Activate If Row < "" Then MsgBox "Found at row " & ActiveCell.Row End If End Sub The Find method works fine for the search of "4" whcih it finds at row 11, but instead of simply not finding the value "15", it causes a runtime error "Object variable or With block variable not set". How can I use the Find method so that this error doesn't occur just because it can't find that particular value? I was hoping it would return zero or false or something similar to indicate that the value wasn't found. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using the Find method in a VBA macro
Hi Mudd,
Try something like: Sub findRow() Dim rng As Range, rng1 As Range Dim searchStr As Variant Dim msg As String searchStr = "4" With ActiveWorkbook.Worksheets("Sheet1") Set rng = .Range(Range("A9"), Range("A9").End(xlDown)) End With Set rng1 = rng.Find(searchStr, LookIn:=xlValues, _ LookAt:=xlWhole) If Not rng1 Is Nothing Then msg = "The search value """ & searchStr _ & """ found at " & rng1.Address Else msg = "The search value """ & searchStr & """ not found" End If MsgBox msg, vbInformation, "Search Result" End Sub --- Regards, Norman "Mudd" wrote in message ... I have got a working example of using the Find method in a VBA macro to get the cell row of the value being searched for, but it only works if the value being searched for exists in the specified range. If the value being searched for is not in the range, then a runtime error occurs. My test contains a worksheet with the numbers 1 to 12 in cells A8 to A19. Here is my macro; Sub findRow() ' Worksheet contains the numbers 1 to 12 in cells A8 to A19. Dim lngMaxRow As Long Dim Row Range("A9").Select Selection.End(xlDown).Select lngMaxRow = ActiveCell.Row Range("A8:A" & lngMaxRow).Select 'Look for 4 Row = Selection.Find(what:="4", LookIn:=xlValues, LookAt:=xlWhole).Activate If Row < "" Then MsgBox "Found at row " & ActiveCell.Row End If 'Look for 15 Row = Selection.Find(what:="15", LookIn:=xlValues, LookAt:=xlWhole).Activate If Row < "" Then MsgBox "Found at row " & ActiveCell.Row End If End Sub The Find method works fine for the search of "4" whcih it finds at row 11, but instead of simply not finding the value "15", it causes a runtime error "Object variable or With block variable not set". How can I use the Find method so that this error doesn't occur just because it can't find that particular value? I was hoping it would return zero or false or something similar to indicate that the value wasn't found. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find method | Excel Programming | |||
Find method | Excel Programming | |||
Find Method | Excel Programming | |||
The find method | Excel Programming | |||
The Find Method | Excel Programming |