With 'Set StrVal=ActiveCell.Address' and ‘Dim StrVal As String’ I get 'Object required' error With ‘Dim StrVal As Range’, a ‘Type Mismatch’ error

[Cozkincaud]

I need to copy the ActiveCell.Address to a string variable to test if a cell is in a range for the Intersect function. I would be very grateful to anyone who could resolve this problem for me. I would also be grateful for any suggestions or comments on the other code in this macro.

My thanks in advance for your comments and suggestions. Here is the macro:

Dim QualifierRange As Range
Dim StrVal As Range ‘NOTE: I also run the macro with "StrVall As String"
QualifierRange = Sheets("Tags III").Range("E43:E50"
Set QualifierRange = Range
Set StrVal = ActiveCell.Address 'Error is 'Type Mismatch' if I Dim Strval as 'Range'. If I Dim it as 'String' i get an 'Object required' error.
If Intersect(QualifierRange, StrVal) Is Nothing Then
ActiveCell.Select
Selection.Copy
Sheets("Tags Insert").Select
Range("G14").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Else
ActiveCell.Select
Selection.Copy
Sheets("Tags Insert").Select
Range("G8").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
End If
End Sub

The macro is intended to copy a cell value and then paste it in another worksheet (TagsInsert). Where It will be pasted in this other sheet (TagsInsert) depends on if the copied cell is in a specified range (QualifierRange)

 

[Cozkincaud]

Sorry! There is a typo in my macro. The line 'QualifierRange = Sheets("Tags III").Range("E43:E50"', should have a closing bracket. When I run my macro this line does have a closing bracket. So a missing bracket is not the problem. Again, apologies for my typing error.

 

[dreid1011]

If you Dim 'StrVal' as a String, you need to remove the 'Set' from the line:

Set StrVal = ActiveCell.Address

to

StrVal = ActiveCell.Address

If you Dim StrVal as a Range, then perhaps just this:

Set StrVal = ActiveCell

 

[Cozkincaud]

If you Dim 'StrVal' as a String, you need to remove the 'Set' from the line:

Set StrVal = ActiveCell.Address

to

StrVal = ActiveCell.Address

If you Dim StrVal as a Range, then perhaps just this:

Set StrVal = ActiveCell

Thanks for your suggestions, but there is still a problem:

If I run the macro with Dim StrVal As String and StrVal = ActiveCell.Address I get a '"Type Mismatch" error

If I run it with Dim StrVal As Range and Strval = ActiveCell (no 'set' function' and no '.address' for ActiveCell), .I get Run-time error 91, "Object variable or with block variable not set". Does this mean I need the set function if I use Dim StrVal As Range?

I cannot see that I can drop address from 'ActiveCell.Address' as it is the address of the cell that I need to pass the StrVal for the following Intersect statement (If Intersect(QualifierRange, CurrentCell) Is Nothing Then).

 

[dreid1011]

If I run the macro with Dim StrVal As String and StrVal = ActiveCell.Address I get a '"Type Mismatch" error

I didn't test this part, so I am not entirely sure of the solution.

If I run it with Dim StrVal As Range and Strval = ActiveCell (no 'set' function' and no '.address' for ActiveCell), .I get Run-time error 91, "Object variable or with block variable not set". Does this mean I need the set function if I use Dim StrVal As Range?

Yes, exactly. If you are using a variable declared as a Range, you have to use 'Set' with it, and I believe you can omit '.Address' but try it both ways and see what works.

 

[MARK858]

@Cozkincaud what happens with the code below?

Sub Cozkincaud()
    Dim QualifierRange As Range
    Dim StrVal As String                         'NOTE: I also run the macro with "StrVall As String"

    Set QualifierRange = Sheets("Tags III").Range("E43:E50")

    StrVal = ActiveCell.Address                  'Error is 'Type Mismatch' if I Dim Strval as 'Range'. If I Dim it as 'String' i get an 'Object required' error.

    If Intersect(QualifierRange, Range(StrVal)) Is Nothing Then
        ActiveCell.Copy Sheets("Tags Insert").Range("G14").End(xlUp).Offset(1, 0)
    Else
        ActiveCell.Copy Sheets("Tags Insert").Range("G8").End(xlUp).Offset(1, 0)
    End If
End Sub

 

[Cozkincaud]

Works like a charm! Thanks so much. I'm very greatful for your help.

 

[MARK858]

You're welcome, happy we could help