CODEWITHSUNDEEP
excelvba
lindqmx1
lindqmx1

Reputation: 89

Run-time error 424 Object Required

I want to find the date I type into an input box in a selected row. I am getting the

Run-time error 91 Object variable or With block variable not Set

error at the f.Select line. Please advise what I did wrong. Thanks much.

Sub Add_Batch_Macro()
Dim mRange As Range
Dim New_Batch_Date As Date
Dim f As Variant
Range("A1").Select
ActiveCell.Rows("1:5").EntireRow.Select
Selection.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste
wsDate_Header_Row = ActiveCell.Row
var_Batch_Qty = InputBox("Batch Quantity is:", "Enter the Batch Quantity:", "")
ActiveCell.Offset(4, 3).Select
Selection.Value = var_Batch_Qty
ActiveCell.Offset(-2, 1).Select
ActiveCell.FormulaR1C1 = "=R[2]C4-R[-1]C"
Application.CutCopyMode = False
New_Batch_Date = InputBox("Use Format: mmddyyyy", "Enter the New Batch Date:", "")
Application.Rows(wsDate_Header_Row).Select
Set mRange = ActiveSheet.Range(ActiveSheet.Cells(wsDate_Header_Row, 1), ActiveSheet.Cells(wsDate_Header_Row, 20))
Set f = mRange.Find(What:=New_Batch_Date, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False)
f.Select
End Sub

Upvotes: 0

Views: 500

Answers (2)

findwindow
findwindow

Reputation: 3153

Edited per OP comment on other answer.

Dim mRange  As Range
Dim New_Batch_Date As Date
Dim f As Variant

wsDate_Header_Row = ActiveCell.Row
New_Batch_Date = InputBox("Use Format: mm-dd-yyyy", "Enter the New Batch Date:", "")

Set mRange = Range(Cells(wsDate_Header_Row, 1), Cells(wsDate_Header_Row, 20))

Set f = mRange.Find(What:=New_Batch_Date, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

f.Select

Upvotes: 1

Bob Phillips
Bob Phillips

Reputation: 437

When you try to create a range object variable, you have to set it to an range object. When you use Set mRange = wsDate_Header_Row you are trying to set it to a row number not an object. You should use something like Set mRange = Cells(wsDate_Header_Row, column_number_or_letter) where column_number_or_letter points to your target column.

Upvotes: 0

Related Questions