1

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.

Geo Koro
  • 75
  • 7
  • `ProdLinUppRan = Range(AdmSh.Range("B10")).Offset(69).Address` [means](https://stackoverflow.com/a/18051644/11683) `ProdLinUppRan.Value = Range(AdmSh.Range("B10")).Offset(69).Address`. `ProdLinUppRan` is `Nothing`. Hence the error. – GSerg Apr 06 '21 at 08:40
  • 1
    You have declared `ProdLinUppRan As Range`. Declare it `As String` since you are storing the address in it. – Siddharth Rout Apr 06 '21 at 08:44
  • 1
    You would have got the same error for `ProdLinLowRan` but since you declared it as Variant (didn't specify anything) so it took it as String. – Siddharth Rout Apr 06 '21 at 08:45
  • If you need the ranges then use: `Dim ProdLinLowRan As Range: Set ProdLinLowRan = AdmSh.Range("B10").Offset(1)` and `Dim ProdLinUppRan As Range: Set ProdLinUppRan = AdmSh.Range("B10").Offset(69)`. When declaring variables in one line each of them has to have an `As` or it will be declared as `Variant`. – VBasic2008 Apr 06 '21 at 08:47
  • :-o !!!!! I see know, thank you all! – Geo Koro Apr 06 '21 at 08:49

2 Answers2

2

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.

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Thank you for that. I'm not experience so I thought you can type once `Dim` and with `,` you can indicate the type... I need to have a better read for those basic staff. – Geo Koro Apr 06 '21 at 09:05
1

There are multiple issues with your code.

a) Your variable declaration is flawed. You will need to specify the variable type for every variable separately. If you write

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

The variables AdmSh and ProdLinLowRan will not be of type Worksheet resp. Range, they will be of type Variant. You need to use (split into 2 lines just for readability)

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

b) AdmSh.Range("B10").Offset(69) is already a Range. No need to write Range around it.

c) If you want to assign an object (and a Range is an object), you need to specify Set.

Set ProdLinLowRan= AdmSh.Range("B10").Offset(1)
Set ProdLinUppRan = AdmSh.Range("B10").Offset(69)

What currently is going on in your code with the lines:

ProdLinLowRan = Range(AdmSh.Range("B10")).Offset(1).Address
ProdLinUppRan = Range(AdmSh.Range("B10")).Offset(69).Address

The right part (Range(AdmSh.Range("B10")).Offset(1).Address) will return the address of a range as String ($B$11). As ProdLinLowRan is declared as Variant, VBA will write that string into ProdLinLowRan and assume that you have a String variable. Not what you intended, but possible.

However, ProdLinUppRan is declared as Range, but not assigned (in VBA terms, it is Nothing). Writing something into a Range writes something into a cell, eg ProdLinUppRan = 3 would write the number 3 into that cell, but only if the range variable is pointing to something. As this is not the case, you will get the runtime error.

FunThomas
  • 23,043
  • 3
  • 18
  • 34
  • Wow! Thank you! I really appreciate your time to explain. You saw what info I’m missing. – Geo Koro Apr 06 '21 at 09:51
  • Btw, the reason I have `Range(AdmSh.Range("B10")).Offset(1).Address` is because I'm trying to get the `Range` from a worksheet as I'm adding columns and row continuously – Geo Koro Apr 06 '21 at 10:01
  • But you neither need to convert a Range into a Range nor you need it's address. – FunThomas Apr 06 '21 at 10:03
  • How then? I'd like to use the `Range("Z3")` in the code. By using `Range(AdmSh.Range("B10")).Offset(1).Address` I get to use `Range("Z3")` even if I change something on the `Worksheet("AdminSheet")`. – Geo Koro Apr 06 '21 at 10:11
  • I think you are on a wrong path here, but discussing this in comments is not the right place. Maybe you should ask a new question with the exact details what you want to achieve. – FunThomas Apr 06 '21 at 10:14