Set Named Ranges as Integers

Question

I'm relatively new to VBA and I'm currently trying to make my life easier by refering to named ranges rather than specific cells (I'm still working on the overall workbook, and I don't want to keep having to change the references every time a cell moves). As far as I know, it shouldn't be a big issue to use a named range rather than the cell address?

The below code is a specific part of a bigger code that I'm working on. It simply inputs the value from one cell (which contains a formula in the worksheet) to the cell above it. I can make this happen with the code "wsi.Cells(19, 10).Value = Cells(20, 10).Value", but I can't make it work using named ranges set as integers.

I've tried a couple of variations, but I keep hitting errors, so any advice would be great!

Edit: Deleted a couple of lines of code that were irrelevant to the post.

Sub Test1()

Application.CutCopyMode = False

Dim wsi         As Worksheet
Dim StartA      As Integer
Dim StartB      As Integer

Set wsi = Worksheets("Input")

StartA = wsi.Range("In_StartA")
StartB = wsi.Range("In_StartB")

StartA = StartB

End Sub

Pᴇʜ · Accepted Answer · 2020-01-20T16:13:41.640

2

You need to define them as Range not Integer.

The following 2 lines are the same.

StartA = wsi.Range("In_StartA")
StartA = wsi.Range("In_StartA").Value

If you omit .Value it is still the default. But in the second line you see clearly what happens: The value of range In_StartA is written into the variable StartA (there is no link to the range because StartA is a numeric value Integer and not a range object).

But if you declare the variables as ranges …

Dim StartA      As Range
Dim StartB      As Range

… and set the variables to the range …

Set StartA = wsi.Range("In_StartA")
Set StartB = wsi.Range("In_StartB")

… then the following line will change the value in the cell In_StartA …

StartA = StartB

… because it is actually the same as

StartA.Value = StartB.Value

… where you can clearly see that both variables are range objects.

So the following should work:

Sub Test1()
    Application.CutCopyMode = False

    Dim wsi         As Worksheet
    Set wsi = ThisWorksheet.Worksheets("Input")

    Dim StartA      As Range
    Set StartA = wsi.Range("In_StartA")

    Dim StartB      As Range        
    Set StartB = wsi.Range("In_StartB")

    StartA.Value = StartB.Value
End Sub

edited Jan 20 '20 at 16:13

answered Jan 20 '20 at 15:24

Pᴇʜ

56,719
10
49
73

Thank you for the answer suggestion, and for clarifying that .Value is assumed by default (I had wondered why some of my macros have them and some don't). I have made the changes you suggested but I'm now receiving the following error: "Run-time error: '1004': Method 'Range' of object ' _Worksheet' failed" The error comes up when I get to the line "Set StartA = wsi.Range("In_StartA")" – MinaSiciliana Jan 20 '20 at 16:04
@user12748650 Can you edit and append the code that you use now to your original question, it's impossible to say what is wrong. Did you set `wsi` correctly and does the named range `In_StartA` exist in sheet `wsi`? See my edited answer for an example. – Pᴇʜ Jan 20 '20 at 16:11
1

Sorry for not updating the code, I'm still getting used to how posting/replying/editing works on here. I checked and there was an inconsistency with the named ranges (can't believe I missed that) so it is working now; thank you so much! – MinaSiciliana Jan 20 '20 at 16:27

Set Named Ranges as Integers

1 Answers1