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

 

[jasonb75]

Welcome to Mr Excel

Looking at your code, searchRow is a range object, but .Cells(searchRow, "A") is expecting a number for a row, the 2 different data types are not compatible in this way.
You should be able to use either of the following lines, as long as there are no other anomalies in your code preventing this from working correctly. I get the impression from your post that you prefer to try and solve the problem yourself so I have only looked at the line that you asked about.

If val1 = Cells(searchRow.Row, "A").Value And val2 >= Cells(searchRow.Row, "C").Value And val3 <= Cells(searchRow.Row, "D").Value Then

If val1 = searchRow(1).Value And val2 >= searchRow(3).Value And val3 <= searchRow(4).Value Then

 

[Legacy 458280]

@jasonb75 Thanks for your response! Unfortunately the code you suggested still gives the same error message. When hovering over assignRow = searchRow.row, it says 'assignRow = Nothing' and 'searchRow.row = 3'. I'm not sure if this helps explain the error any further?

Thanks

 

[jasonb75]

Try

Set assignRow = searchRow

Because assignRow is declared as Range, it needs to be set as a range, not a row.

Not sure of the best way to try and explain the difference, see if this makes sense.

Range is an object
searchRow and assignRow are delcared (Dim) as Range, so they are also objects.
searchRow.Row is a number (the row number of the object).
searchRow.EntireRow is an object (the entire row of seachRow as a Range).

 

[Legacy 458280]

That makes sense, thanks. Still getting the same error on the "If, Then" line though. Although it's slightly different now; since making the change you suggested, hovering over the 'searchRow' part of Set assignRow = searchRow gives no value ('assignRow' still gives "assignRow = Nothing". At this point I'm completely lost - I've tried as many workarounds as I can think of, including advanced filtering, etc. but I just can't seem to get my head round it. Any other suggestions? Thanks again for taking the time to help with this.

 

[jasonb75]

Which of the 2 lines are you using from my first reply.

The first one will most likely error if searchRng is not in the activesheet, but the second one should work regardless. If not, then there is something else that I'm missing.

 

[Legacy 458280]

I'm using the second line. Thanks.

 

[jasonb75]

I don't think that it makes any difference, but try

If val1 = searchRow(1, 1).Value And val2 >= searchRow(1, 3).Value And val3 <= searchRow(1, 4).Value Then

Is it still a type mismatch error, or a different error now?
A type mismatch only happens when you use the wrong data type (using range where it should be a number, etc) but I can see nothing else in this line of code that would cause that error.
Try hovering over val1, val2, val3 and each of the 3 .Values do any of those show any error messages?

 

[Legacy 458280]

That changes it to a "Run-time error '1004' - Application-defined or object-defined error".
val1, val2 and val3 all working as they should.
The three .Values show no value though (i.e. nothing happens when hovering).
No change on Set assignRow = searchRow.

 

[jasonb75]

Not sure why it's not accepting the indexing method that I suggested, but this one works (at least in a simple test).

If val1 = searchRow.Cells(1, 1).Value And val2 >= searchRow.Cells(1, 3).Value And val3 <= searchRow.Cells(1, 4).Value Then