Find Value; Return Row number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Goodmorning everybody!

I was wondering if someone could help me with the following problem:

"I wanted to make a macro which would find the first value (counting from
a1) that matches the value stated in C14 of "FORM". When it gives a match I
want to return the row number to "Sheet2".

I tested it with dates of which I was certain it had to find a match (as I
entered them myself in "Sheet1") Still it returns:

"Named Argument not Found" (Runtime error 448)"

I hope someon sees my mistake(s). Thanks in Advance!!

Sub TestDelete()

l = Sheets("FORM").Range("A1").Value
Sheets("Sheet1").Select
Cells.Select
Range("E1").Activate
k = Selection.Find(What:=Worksheets("FORM").Range("C14").Value,
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
Sheets("Sheet2").Range("A1").FormulaR1C1 = "" & k & ""

End Sub
 
Jasper
You say C14 in the FORM sheet but your code says A1 of the FORM sheet.
Also, you don't need to select any sheets.
Try this:
Sub TestDelete()
Dim SearchFor As Variant
Dim TheRow As Long
SearchFor = Sheets("FORM").Range("C14").Value
With Sheets("Sheet1")
TheRow = .Cells.Find(What:=SearchFor, After:=.Range("E1"),
LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Row
End With
Sheets("Sheet2").Range("A1").Value = TheRow
End Sub

HTH Otto
 
Goodevening Otto,

thanks for the help so far. It still returned "Named Argument not Found"
(Runtime error 448)" I altered it a bit so it would look for something in the
same format, still it returned "Named Argument not Found" (Runtime error
448)" on:

TheRow = .Cells.Find(What:=SearchFor, After:=.Range("E1"),
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Row

Do you have any idees? I am working with Excel 2000 and Windows 2k. Thanks
in advance!

Jasper


Sub TestDelete3()
Dim SearchFor As Variant
Dim TheRow As Long

Sheets("Sheet1").Select
Columns("E:E").Select
Selection.NumberFormat = "0"

Sheets("FORM").Select
Range("C1").Select
Selection.NumberFormat = "0"

SearchFor = Sheets("FORM").Range("C1").Value
With Sheets("Sheet1")
TheRow = .Cells.Find(What:=SearchFor, After:=.Range("E1"),
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
End With
Sheets("Sheet2").Range("A1").Value = TheRow

End Sub
 
i think the Find method in Excel 2000 does'nt have the named argument of
SearchFormat in Arguments.
but Excel 2003 has it. so, if you want Find method to work in Excel 2000, you
might need to delete the SearchFormat:=False in Arguments.

keizi kounoike
 
Back
Top