PDA

View Full Version : Variant type not match error



Ken585
08-27-2009, 01:32 AM
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

tpoynton
08-27-2009, 05:28 AM
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.

Ken585
08-27-2009, 07:31 PM
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:


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