VBA - Referring to cell using row and column variables

[Legacy 458280]

Hi,

I'm having a bit of trouble with referring to a cell using row and column variables. For some reason, it appears to be returning the value rather than the cell address for "startCell =" and "endCell ="

The code is as follows (the area of interest is below the second commented line [posted full code for clarity]):

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 Lastrow As Long
    
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 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

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

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

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


Dim find_Start As String

Dim find_End As String

find_Start = Format(startDate, "Short Date")
find_End = Format(endDate, "Short Date")


Set startColumnRaw = ThisWorkbook.Sheets("Board").UsedRange.Find(what:=CDate(find_Start), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows)
Set endColumnRaw = ThisWorkbook.Sheets("Board").UsedRange.Find(what:=CDate(find_End), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows)

startColumn = startColumnRaw.Column
endColumn = endColumnRaw.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

searchRow.EntireRow.Delete

ThisWorkbook.Sheets("Board").Activate

Application.ScreenUpdating = True

End Sub

... where:

• selectRow = ActiveCell.Row (defined at the beginning of the code)
• startColumn is correctly defined as a ".Column" (this is working fine - pulls the correct values through)
• endColumn is correctly defined as a ".Column" (this is working fine - pulls the correct values through)

I can't get my head round why this isn't working. Any suggestions would be welcome. If you need any further information, please let me know.

Thanks in advance,
Luke

 

[Fluff]

Change the declarations of both variables to Range & use this

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

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

assignmentRange.Clear

 

[Legacy 458280]

That works! Thank you very much for your help.

 

[Fluff]

You're welcome & thanks for the feedback