Excel VBA Range.Value is Variant instead of String

Question

I have a Macro enabled Excel workbook, and when it launches, it edits some cells and I have a change event, now I want the change event to not run at start up but run when the user makes the changes because when it does, the target.value becomes a variant and hence crashes as am assigning the value to a string variable, here is the code:

If Not Intersect(Target, Range("H83:H102,K83:K102,N83:N102,Q83:Q102,T83:T102,W83:W102,Z83:Z102,AC83:AC102")) Is Nothing Then
    Dim rowNum As Integer
    rowNum = Target.row
    Dim family As String
    family = Target.Offset(0, -1).Value
    Dim selection As String
    selection = "G" & rowNum & ",J" & rowNum & ",M" & rowNum & ",P" & rowNum & ",S" & rowNum & _
        ",V" & rowNum & ",Y" & rowNum & ",AB" & rowNum
    Select Case family
        Case "FAMILY 1"
            Call validateFamMembers(Target, selection, family, "C64")
        Case "FAMILY 2"
            Call validateFamMembers(Target, selection, family, "I64")
        Case "FAMILY 3"
            Call validateFamMembers(Target, selection, family, "O64")
        Case "FAMILY 4"
            Call validateFamMembers(Target, selection, family, "U64")
        Case "FAMILY 5"
            Call validateFamMembers(Target, selection, family, "C76")
        Case "FAMILY 6"
            Call validateFamMembers(Target, selection, family, "I76")
        Case "FAMILY 7"
            Call validateFamMembers(Target, selection, family, "O76")
        Case "FAMILY 8"
            Call validateFamMembers(Target, selection, family, "U76")
    End Select
End If

The code crashes on this line on start up

family = Target.Offset(0, -1).Value

how do I handle that. Thanks in advance

Range.Value is supposed to be Variant...not String. If you need to force it to a string use CStr() — Rdster
– Rdster, Commented Dec 1, 2016 at 13:55
@CallumDA33 The code is very big but that is the chunk that executes on worksheet change, its that if among a bunch of other ifs but they are independent — lulliezy
– lulliezy, Commented Dec 1, 2016 at 14:16
@Rdster I tried using that but still type mismatch when i restart the workbook — lulliezy
– lulliezy, Commented Dec 1, 2016 at 14:17

Vityata · Accepted Answer · 2016-12-01 14:53:19Z

2

I think your problem happens when you select the first column. It explodes, because of the offset, because it cannot reach -1 column after the first.

Edit:

Answer in the comments: You use Target. Thus, if you select more cells, more cells are in the target. And multiple cells cannot be converted to string, because they are a variant.

edited Dec 1, 2016 at 14:53

answered Dec 1, 2016 at 14:00

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Comintern Over a year ago

Yep. Was just about to post that as a comment. The guard clause only makes sure the changed range contains cells the OP cares about.

CallumDA Over a year ago

Does that answer your question though?

Vityata Over a year ago

@lulliezy, can you put the line debug.print Target.Offset(0, -1).Value before the line giving you the error and share the answer? There should be a value in the immediate window.

lulliezy Over a year ago

Type mismatch on Debug.Print Target.Offset(0, -1).Value @Vityata

lulliezy Over a year ago

@Vityata $E$83:$AB$102 this is the value that is printed

|

Collectives™ on Stack Overflow

Excel VBA Range.Value is Variant instead of String

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related