Need to know if a variable can be placed in a range

[Bill_Biggs]

Here is a portion of a macro I am working on:

<font face=Courier New>YesNo = MsgBox("Are you sure you want " & Z & " ?", vbYesNo)<br>    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> YesNo<br>    <SPAN style="color:#00007F">Case</SPAN> vbNo<br>    <SPAN style="color:#00007F">GoTo</SPAN> Alpha<br>    <SPAN style="color:#00007F">Case</SPAN> vbYes<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>   </FONT>
<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> value <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Set</SPAN> rng1 = Range("C3:E6")<br>varRanges = VBA.Array("B100", "C100", "D100", "E100", "F100", "G100", "H100", "I100", "J100", "K100", "L100", "f100")<br></FONT>

I need to know if there is a way I can get into the range (VBA.Array) and place a variable that can take on the Z values instead of the the 100's? So if Z=95 the the array would read as B95, C95 etc...

Thanks,

 

[Case_Germany]

Hi,

try:

Sub Test()
    Dim varRanges As Variant
    Dim intTMP As Integer
    intTMP = 95
    varRanges = VBA.Array("B" & intTMP, "C" & intTMP, "D" & intTMP)
    MsgBox varRanges(0)
End Sub

Case_Germany

 

[jindon]

Another way

Dim myRow As Long
myRow = 95
varRanges = Split(Join(Split("B C D E F G H I J K F"),myRow & ","),",")

 

[Bill_Biggs]

Thank you both!!

 

[Case_Germany]

Hi jindon,

this is a treat - the nesting of the functions (Split, Join, Split) is a super idea.

But a question.

If I go through the code with F8 step by step I notice the following - Join does not consider probably the last entry.

The only way which I found, except the function to be split, is following:

varRanges = Split(Join(Split("B C D E F G H I J K F "), myRow & ","), ",")

A blank after the last value.

OK - it functions when I split the code like this and during the writing the last entry omitted.:

Dim varRanges
    Dim myRow As Long
    myRow = 95
    varRanges = Split("B C D E F G H I J K F", " ")
    varRanges = Join(varRanges, myRow & ",")
    varRanges = varRanges & myRow
    varRanges = Split(varRanges, ",")

Is there a way to put it in just one codeline?

Case_Germany

 

[Norie]

One line.

    varRanges = Split(Join(Split("B C D E F G H I J K F", " "), myRow & ",") & myRow, ",")

 

[Case_Germany]

Hi Norie,

Thanks

Case_Germany

 

[Norie]

Case

No problem.

Though I do think constructs like that could confuse matters somewhat.

Split, Join, Split... where will it end.

Mind you I'm not actually sure what the purpose of the array is in the OP's code.

Especially since column F appears twice.