CODEWITHSUNDEEP
vbaexcelrange
ThatQuantDude
ThatQuantDude

Reputation: 833

VBA Range change selection, value doesn't change

I have a range variable (called Constr) that is based on data that looks like this

Type    Bound1    Bound2    Var1    Var2
X       1         2         3       4
Y       1         2         3       4
--  
Z       1         2         3       4

I now use this procedure to change the selection to only the entries before the '--' 

Sub Adjust_Selection(which As String, what_in As String, columns As Integer)
    Dim row_nr_start As Integer
    Dim row_nr_end As Integer
    Dim row_nr_delta As Integer

    Sheets("Main").Select
    row_nr_start = Range(which).Find(what:=what_in, LookIn:=xlValues, LookAt:=xlWhole).Row
    row_nr_end = Range(which).Find(what:="--", LookIn:=xlValues, LookAt:=xlWhole).Row

    row_nr_delta = row_nr_end - row_nr_start

    Range(which).Resize(row_nr_delta, columns).Select

This works and I can see that the selection changes, if I now call it using

Call Adjust_Selection("Constr", "Type", 5)
myitem("Constraints") = Range("Constr").Value

myitem is of type

Dim myitem As New Scripting.Dictionary

however when I access the value it still has everything in it. How can I update the value to only the first few lines up until the '--'?

Upvotes: 1

Views: 568

Answers (3)

Ralph
Ralph

Reputation: 9434

You are calling Adjust_Selection with the named range Constr and afterwards refer to the named range Constraints. So, of course the result is different because you are referring to two different named ranges.

Furthermore, the named range Constr is not altered. It is merely used as a starting point and then a sub-set is Selected. But by selecting something you are not changing a named range (especially not a differently named range).

So, I am guessing that this is what you are searching for:

Call Adjust_Selection("Constr", "Type", 5)
ThisWorkbook.Names.Add Name:="Constraints", RefersTo:=Selection
myitem("Constraints") = Range("Constraints").Value

Note, that the selection of Adjust_Selection is now "saved" in the new named range Constraints and then myitem is being assigned this named range which is limited to the (correct) selection. Hence, the resulting variable (being a dictionary) contains all elements without the --.

Upvotes: 1

Excelosaurus
Excelosaurus

Reputation: 2849

Range.Resize is a function. It will not change the range; it returns a new one. You just happen to select it, which isn't necessary. Turn your sub into a function returning the result of Range.Resize, and use this function directly on the right hand side of your assignment. Note that you're not using the same name for your range in both lines of code, which I assume is a typo.

Upvotes: 0

Tommy L
Tommy L

Reputation: 1

Hi ThatQuantDude, I don't quite understand your question even after trying it out on my own. Based on the examples you gave, I assumed you want to store the selected range data into "Constraints" key? Apart from this, your sub function for selecting the range is working fine.

Call Adjust_Selection("Constr", "Type", 5)
myitem("Constraints") = Range("Constraints").Value

Appreciate if you could elaborate it further so I can better understand what you are trying to do? Thanks.

Upvotes: 0

Related Questions