CODEWITHSUNDEEP
vbaexcel
user3795861
user3795861

Reputation: 61

Type mismatch error in vba using activecell

I am getting an error which is type mismatch.. i am trying to set up active cell equal to certain cell. If activecell = E4:E18 then do this here is my code can anyone tell me what I'm doing wrong in here. 

        If ActiveCell.Row = Range(Cells(4, 5), Cells(18, 5)) Then
              ActiveSheet.Cells(ActiveCell.Row, 4) = Now
        If ActiveCell.Row = Range(Cells(4, 8), Cells(18, 8)) Then
              ActiveSheet.Cells(ActiveCell.Row, 7) = Now

Which is I'm trying to set up active cell = E4 to E18 then this '

Upvotes: 0

Views: 811

Answers (1)

ChipsLetten
ChipsLetten

Reputation: 2953

You are trying to compare a number ActiveCell.Row against a range object which is what the Range method returns. You would need to use this:

If ActiveCell.Row = Range(Cells(4, 5), Cells(18, 5)).Row Then

However, this will not work how you want it to. The ActiveCell is only ever a single cell, so the Row property is only a single number. The Selection range can be more than a single cell. However, the Row property still only returns the row number for the first row of cells.

If you are trying to check if the ActiveCell is within the range E4:E18, then use the Intersect method to see if returns a range object:

If Not (Intersect(ActiveCell, Range(Cells(4, 5), Cells(18, 5))) Is Nothing) Then
    MsgBox "Yes"
Else
    MsgBox "No"
End If

If you are just concerned with the row number, then use

If (ActiveCell.Row >= 4 And ActiveCell.Row <= 18) Then
    MsgBox "Yes"
Else
    MsgBox "No"
End If

Upvotes: 1

Related Questions