I am getting an error 'Object variable or With block variable not set' when I run this…
- User
I am getting an error 'Object variable or With block variable not set' when I run this code:
Sub CopyReminderToEvents() 'Application.EnableEvents = False Dim remindersSheet As Worksheet Dim eventsSheet As Worksheet Set remindersSheet = ThisWorkbook.Sheets("Reminders") Set eventsSheet = ThisWorkbook.Sheets("Events") Dim searchRange As Range Set searchRange = remindersSheet.Range("H:H") Dim currentRow As Long
If Not Application.Selection Is Nothing Then If Not Intersect(searchRange, Application.Selection) Is Nothing Then currentRow = Application.Selection.Row If searchRange.Find(What:=Application.Selection.Value) Is Nothing Then 'MsgBox "Value not found in column H.", vbInformation, "Search Error" 'Else currentRow = searchRange.Find(What:=Application.Selection.Value).Row ' rest of the code for copying to Events sheet
' Use the currentRow to get the value from column H of the selected row Dim contractorName As String contractorName = remindersSheet.Cells(currentRow, "H").Value ' Get the values from columns E, F, and H in the selected row Dim eventDate As Date Dim eventInfo As String eventDate = remindersSheet.Cells(currentRow, "F").Value eventInfo = remindersSheet.Cells(currentRow, "E").Value contractorName = remindersSheet.Cells(currentRow, "H").Value
' Find the last empty row in Events sheet Dim lastRow As Integer lastRow = eventsSheet.Cells(eventsSheet.Rows.count, "B").End(xlUp).Row + 1
' Copy the values to the last empty row in Events sheet eventsSheet.Cells(lastRow, "B").Value = eventDate eventsSheet.Cells(lastRow, "C").Value = eventInfo eventsSheet.Cells(lastRow, "D").Value = contractorName
'Else 'MsgBox "Please select a cell within column H.", vbInformation, "Selection Error" End If 'Else 'MsgBox "Please select a cell before running this macro.", vbInformation, "Selection Error" End If End If Application.EnableEvents = True
End Sub
- Assistant
The error 'Object variable or With block variable not set' usually occurs when a variable is not properly initialized or assigned to an object. In this case, there are a few variables that may be causing the error.
One possible cause is that the worksheet names "Reminders" and "Events" may not exist in the workbook. Ensure that the names are spelled correctly and that the sheets exist in the workbook.
Another possible cause is that the variable "searchRange" may not have been properly initialized. Ensure that the range "H:H" exists in the worksheet "Reminders".
Lastly, it is possible that the selection made by the user may not be within the search range. Ensure that the selection made by the user is within column H of the "Reminders" sheet.
To debug the error, try adding some error handling code or use the debugger to step through the code and find the exact line causing the error.