CODEWITHSUNDEEP
excelvba
FreeSoftwareServers
FreeSoftwareServers

Reputation: 2831

Hard Coding Range works but using Named Range failes - Excel VBA

I have a code snippet that I can't understand why it's failing. I get "Application-Defined or Object-Defined Error". I tried hard coding the range and it does work, but I even hardcoded the "Named Range" and it still failed.

Eg: This Doesn't Work

If regexp.test(strInput) Then
    Set clloffset = rcell.Offset(0, 1)
    Call GetColLet(rcell)
      'Set PalletCol = Range(rcell.Address(False, False) & ":K39")
      Set PalletCol = ActiveWorkbook.ActiveSheet.Range("K24:K39")
      Set FormulaCol = ActiveWorkbook.ActiveSheet.Range("L24:L39")
      ActiveWorkbook.ActiveSheet.Range(FormulaCol).Formula = "=" & Chr(34) & "BINWH" & Chr(34) & "&" & rcell.Address(0, 1)

But this does and I can't get it!

If regexp.test(strInput) Then
    Set clloffset = rcell.Offset(0, 1)
    Call GetColLet(rcell)
      'Set PalletCol = Range(rcell.Address(False, False) & ":K39")
      Set PalletCol = ActiveWorkbook.ActiveSheet.Range("K24:K39")
      Set FormulaCol = ActiveWorkbook.ActiveSheet.Range("L24:L39")
      ActiveWorkbook.ActiveSheet.Range("L24:L39").Formula = "=" & Chr(34) & "BINWH" & Chr(34) & "&" & rcell.Address(0, 1)

Note: I tried using both (FormulaCol) and ("FormulaCol"), I'm not sure when to use "" inside a named range to be honest, but neither work. Then I tried hard coding ("L24:L39"). and it worked. I appreciate any help troubleshooting this.

Upvotes: 0

Views: 931

Answers (1)

Mistella
Mistella

Reputation: 1738

There are three main methods of accessing ranges in Excel:

  1. Hard-coded values
  2. Variables
  3. Named Ranges

Hard-coded values

I won't really spend time explaining these, but here are some examples:

ThisWorkbook.Sheets("Name").Range("A1:B2")
ActiveWorkbook.ActiveSheet.Range("C3:E50")
Sheet1.Cells(1,3)  'Range("C1")

Notes

ThisWorkbook is a reference to the workbook which holds the code. An important distinction from ActiveWorkbook, as they are not always the same workbook (primarily when 2+ workbooks are open).

Sheet1 is the code name of a sheet. Sheet code names can only be used for the sheets contained in ThisWorkbook. If referencing a sheet from a different workbook, you'd have to use either the name or index to access it.

Variables

This would be like the FormulaCol in your original code. Variables are either declared in the code (Set FormulaCol = ActiveWorkbook.ActiveSheet.Range("L24:L39")) at local or global scope level, or are passed into routines as parameters (Function Sample(rangeVariable as Range) : End Function).

Interacting with a range variable could be thought of as using substitution, such as:

Set FormulaCol = Sheet1.Range("L24:L39")
FormulaCol.Formula = ""

instead of

Sheet1.Range("L24:L39").Formula = ""

Named Ranges

Interacting with a named range is similar to interacting with a hard-coded range. Assuming that the named range "Test" references Range("B2:C3") on Sheet1, it would be interacted with via:

Sheet1.Range("Test")

However, before named ranges can be referenced through VBA, they first have to be set up in the workbook. This can be done a couple of different ways:

  1. Select all the cells for the named range, then in the "Name Box" (to the left of the formula bar) type in the name for that named range, and press enter (if you forget "enter", the name doesn't get saved).

Naming range without manager

  1. Open the Name Manager in the Formulas tab and press "New" (or "Edit", if editing an existing named range). Note: when adding a named range via the manager, you can select the scope to be either the workbook or any of the sheets within the workbook. This affects which sheets can reference the named range.

Name Manager

and a window will open where you choose the name for the named range, and select what range it's referencing.

Adding a named range

Upvotes: 1

Related Questions