Consulting

Results 1 to 3 of 3

Thread: Variant type not match error

  1. #1

    Exclamation Variant type not match error

    I'm trying to realize the function below in Excel:
    If range("A1:C19") is not blank then Range("D1:F19") locked, vice versa.
    If range("A1:C19") and ("D1:F19") are both blank then worksheet is unprotected, range("A1:C19") and ("D1:F19") locked = false.
    But when I run the VBA code which is in blue below, then I'm encountering an error indicated "Variant type not match"
    Could any one help with this, thanks!
    ________________________________________________________________

    Private Sub Worksheet_Change(ByVal Target As Range)
    Call InputLimit
    End Sub


    Sub InputLimit()
    Dim range1 As Range
    Dim range2 As Range
    Dim Sh As Worksheet
    Set Sh = Workbooks("InputLimit").Worksheets("sheet1")
    Set range1 = Workbooks("InputLimit").Worksheets("sheet1").Range("A1:C19")
    Set range2 = Workbooks("InputLimit").Worksheets("sheet1").Range("D1:F19")
    If range1.FormulaR1C1 <> "" And range2.FormulaR1C1 <> "" Then
    Sh.Unprotect
    range1.Locked = False
    range2.Locked = False
    ElseIf range1.FormulaR1C1 <> "" Then
    range2.Locked = True
    Sh.Protect
    ElseIf range2.FormulaR1C1 <> "" Then
    range1.Locked = True
    Sh.Protect
    End If
    End Sub

  2. #2
    VBAX Mentor tpoynton's Avatar
    Joined
    Feb 2005
    Location
    Clinton, MA
    Posts
    399
    Location
    try replacing all .FormulaR1C1 <>"" references with .hasformula = true

    if that doesnt work, post a sample workbook.

    Welcome to the forum! FYI, I dont think this issue is Mac specific, so posting in the Excel forum might get you faster responses.

    AND, you can format code posted in the forum by selecting it and clicking on the VBA button in the editor.

  3. #3
    tpoynton, I have tried your suggestion, it doesn't work and I tried to change the variant type of range1.FormulaR1C1 & range2.FormulaR1C1 to string, the error still come up.
    But I figured out another way to solve this issue.
    Any way, thank you very much for your help!
    Here is the code below:

    [VBA]
    Private Sub Worksheet_Change(ByVal Target As Range)
    Call InputLimit
    End Sub

    Sub InputLimit()
    Dim range1 As Range
    Dim Sh As Worksheet
    Set Sh = Workbooks("InputLimit").Worksheets("sheet1")
    For Each cell In Range("A1:F19")
    If cell.FormulaR1C1 = "" Then
    Sh.Unprotect
    Range("A1:F19").Locked = False
    End If
    Next
    For Each cell In Range("A1:C19")
    If cell.FormulaR1C1 <> "" And Range("D1:F19").Locked = False Then
    Range("D1:F19").Locked = True
    Sh.Protect
    End If
    Next
    For Each cell In Range("D1:F19")
    If cell.FormulaR1C1 <> "" And Range("A1:C19").Locked = False Then
    Range("A1:C19").Locked = True
    Sh.Protect
    End If
    Next
    End Sub
    [/VBA]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •