VBA - set row as variable based on "If, Then" expression

[Legacy 458280]

Hi,

This is my first post. I think I'm posting this in the correct place, but apologies in advance if not. I've been struggling with this line of code for the past few days now, having seemingly searched through Google in its entirety, to no avail...

I'm having trouble setting a row as a variable in VBA. I'm using a "For Each" expression with a nested "If, Then" statement to find the first row that meets the given criteria.

Here is the code I'm using:

Sub DeleteAssignment()
Static selectRow As Long
Dim selectEmployee As String
Dim selectDate As Date

selectRow = ActiveCell.row
selectEmployee = Cells(selectRow, 2).Value

selectDate = Cells(2, ActiveCell.Column).Value

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
  
Dim searchRng As Range
Dim searchRow As Range
Dim assignRow As Range
Dim Lastrow As Long
    
Dim val1 As String
Dim val2 As Date
Dim val3 As Date
    
Application.ScreenUpdating = False
    
    val1 = selectEmployee
    val2 = selectDate
    val3 = selectDate
    
Lastrow = ThisWorkbook.Sheets("Resource Assignment").Cells(Rows.Count, "A").End(xlUp).row

Set searchRng = ThisWorkbook.Sheets("Resource Assignment").Range("A" & 3 & ":D" & Lastrow)

For Each searchRow In searchRng.Rows
If val1 = Cells(searchRow, "A").Value And val2 >= Cells(searchRow, "C").Value And val3 <= Cells(searchRow, "D").Value Then
assignRow = searchRow.row
End If
Next searchRow

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim startDate As Date
Dim endDate As Date
Dim startColumn As Long
Dim endColumn As Long
Dim startCell As Long
Dim endCell As Long
Dim assignmentRange As Range

startDate = assignRow.Cells(1, 3).Value
endDate = assignRow.Cells(1, 4).Value

startColumn = ThisWorkbook.Sheets("Board").Find(startDate, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
endColumn = ThisWorkbook.Sheets("Board").Find(endDate, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

startCell = ThisWorkbook.Sheets("Board").Cells(selectRow, startColumn)
endCell = ThisWorkbook.Sheets("Board").Cells(selectRow, endColumn)

Set assignmentRange = ThisWorkbook.Sheets("Board").Range("startCell:endCell")

assignmentRange.Clear

assignRow.EntireRow.Delete

ThisWorkbook.Sheets("Board").Activate

Application.ScreenUpdating = True

End Sub

The problem is that I get a Type Mismatch error on "If val1 = Cells(searchRow, "A").Value And val2 >= Cells(searchRow, "C").Value And val3 <= Cells(searchRow, "D").Value Then" when running the code. I can't figure out what's causing this, though, as the respective columns are formatted with the correct data types (text, date and date).

The 'val1', 'val2' and 'val3' variables work fine (when running the code, hovering over them shows the correct values) and hovering over "searchRow.Row" appears to give the correct row number, but it won't allow it to be set as a variable, due to the Type Mismatch above.

As a result, I haven't been able to test any of the code below this point without resolving the Type Mismatch error.

Any help would be greatly appreciated. If you need any more information, please do let me know.

Many thanks,
Luke

 

[Legacy 458280]

Okay, so this is giving me an error 91 ("Object variable or With block variable not set"). It says the error is further down in startDate = assignRow.Cells(1,3).Value
BUT...
assignRow is still not successfully set as a variable - I'm not entirely sure why it's decided to ignore that for now and move onto a different error further down the code. When hovering over Set assignRow = searchRow, both sides of the argument give a value of "Nothing", this time.

I seem to remember finding myself in this situation previously, when experimenting with different workarounds and making minor adjustments to the code. My notes tell me that adding 'With' and 'End With' (in various different places) had no effect, still resulting in the error message.

Thanks.

 

[jasonb75]

As long as you checked Set assignRow = searchRow after the code had passed that line then that would mean that no matching row was found where the criteria of val1, val2 and val3 were all met.

 

[Legacy 458280]

Jason,

My mistake. It seems there was a slight oversight on my part. It appears that your last suggestion solved the issue after all. Thank you very much for your help, it has been driving me mad for days!

Cheers,
Luke

 

[jasonb75]

You're welcome

While you were trying to resolve your error, I kept it as close to your original code as possible.
One thing that I did notice was the number of variables that you don't really need, by changing the part where you had the error to

For Each searchRow In searchRng.Rows
    If searchRow.Cells(1, 1).Value = selectEmployee And searchRow.Cells(1, 3).Value <= selectDate And searchRow.Cells(1, 1).Value >= selectDate Then Exit For
Next searchRow

You could do away with 4 variables, val1, val2, val3 and assignRow. By using Exit For once a match is found, you can use searchRow where you would have used assignRow in the later parts of the code. You could do similar with the second part of your code as well.

 

[Legacy 458280]

Even better! Thanks again.