I m getting the error: Runtime error 91: “ Object variable or With block variable not set”

I was asked to design the work allotment process for the company. Work is allotted in the given manner: If suppose a task T1 is allotted to a person for 5 days, it should be displayed against his name for 5 consecutive days in the work allotment file. I have written a Visual basic code in MS Excel using macros in the third sheet - MAY of workallotment.xlsm and the tasks are in file tasks.xlsx . I m getting the error: Runtime error 91: “ Object variable or With block variable not set” while searching for the members name in workallotment.xlsm

I have also tried setting the findrow variable like "set FindRowNumber = rngFindValue.Row" But I get a compiler error saying object required .I attach both the files workallotmetn.xlsm and tasks.xlsx

Even if I end the loop and end sub after the statement "If rngFindValue is Nothing " it still is showing the same runtime error.Please refer to the code below:


Sub Exercise()

Dim Arr As Variant, Arr1 As Variant
Dim iCol As Integer
Dim FindRowNumber As Integer
Dim i As Integer, x As Integer
Dim name As String
Dim rngFindValue As Range




'name = Cells(1, 1).Value
Arr = Workbooks.Open("E:tasks.xlsx").Sheets("Sheet1").Range("B1:E1").Value
Arr1 = Workbooks.Open("E:tasks.xlsx").Sheets("Sheet1").Range("B2:E2").Value
Sheets(1).Cells(1, 1).Select

For i = 1 To Arr1(1, 1)
Cells(6, 4 + i).Value = Arr(1, 1)
a = i + 4
Next i


For i = 1 To Arr1(1, 2)
Cells(6, a + i).Value = Arr(1, 2)
b = a + i
Next i

For i = 1 To Arr1(1, 3)
Cells(6, b + i).Value = Arr(1, 3)
C = b + i
Next i

For i = 1 To Arr1(1, 4)
Cells(6, C + i).Value = Arr(1, 4)
d = a + i
Next i



Do While ActiveCell.Row <> Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
ActiveCell.Offset(2, 0).Select
name = ActiveCell.Value
Arr = Sheets(1).Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 5)).Value
Arr1 = Sheets(1).Range(ActiveCell.Offset(1, 1), ActiveCell.Offset(1, 5)).Value



Set rngFindValue = Sheets(3).Range("A1:A100").find(What:=name, After:=Sheets(3).Range("A1"), LookIn:=xlFormulas)




FindRowNumber = rngFindValue.Row


For i = 1 To Arr1(1, 1)
Cells(FindRowNumber, 4 + i).Value = Arr(1, 1)
a = i + 4

Next i


For i = 1 To Arr1(1, 2)
Cells(FindRowNumber, a + i).Value = Arr(1, 2)
b = a + i
Next i

For i = 1 To Arr1(1, 3)
Cells(FindRowNumber, b + i).Value = Arr(1, 3)
C = b + i
Next i

For i = 1 To Arr1(1, 4)
Cells(FindRowNumber, C + i).Value = Arr(1, 4)
d = a + i
Next i


If Not rngFindValue Is Nothing Then
MsgBox ("not found")
End If

Loop

End Sub

Is this how you want me to change the code ?

Please Login or Register  to view this content.

But I want to assign the values I read from tasks.xls to 2 arrays and then to store in the file workallotment.xlsm in the correct row where the name is found .If I end sub after "If rngFindValue is Nothing " how will I be able to do it?

Are there any updates on this one ?