Search code examples
excelvbaruntime-error

Excel VBA - Why am I getting Runtime Error

Everyone,

Can someone help me understand what is wrong with the following code?

Sub CombDelDuplShNamCellAdd()

Dim ProdVal As New Scripting.Dictionary
ProdVal.Add "ULP", 100000
ProdVal.Add "PULP", 200000
ProdVal.Add "SPULP", 300000
ProdVal.Add "XLSD", 400000
ProdVal.Add "ALPINE", 500000
ProdVal.Add "JET", 600000
ProdVal.Add "SLOPS", 700000


Dim AdmSh, CalSh As Worksheet, ProdLinLowRan, ProdLinUppRan As Range

Set CalSh = Sheets("Calendar")
Set AdmSh = Sheets("AdminSheet")

'''' Data Prep Table
ProdLinLowRan = Range(AdmSh.Range("B10")).Offset(1).Address
ProdLinUppRan = Range(AdmSh.Range("B10")).Offset(69).Address

MsgBox ProdLinLowRan

enter image description here enter image description here

Why am I getting this error? Can't understand. I'm newbie btw.

Cheers.

Solution

  • You have Dim'ed incorrectly.

    Dim AdmSh As Worksheet
Dim CalSh As Worksheet
Dim ProdLinLowRan As Range
Dim ProdLinUppRan As Range

    All these objects need a Set.

    The reason for ProdLinLowRan not failing is, that you dim it as Variant, which is very forgiving.