how do I declare a variable with another variable

[zkaplan]

I am writing a piece of code and I would like to call a variable using another variable for instance:

For i = 1 to 5

srch & i = range("A"& i).value

next

 

[just_jon]

Welcome to MrExcel

You could do something like --

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> foo()
<SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, MyVar(1 <SPAN style="color:#00007F">To</SPAN> 10) <SPAN style="color:#007F00">' declare a variant array</SPAN>
i = 4
MyVar(i) = Cells(i, "a")
MsgBox MyVar(i)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

 

[zkaplan]

thank you for the help. That won't work because I am using it as an input into a function, and it won't allow me to use arrays. Is there any other way to do that. I guess what I am going for is kind of like the indirect function in Excel.

 

[Norie]

Can we see your function?

Are you sure it won't allow you to use an array?

You can't refer to variables in the way you have suggested.

What are you actually trying to achieve?

 

[just_jon]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> foo()
<SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, myvar1, mtvar2, myvar3, myvar4
<SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> i
    <SPAN style="color:#00007F">Case</SPAN> 1
        myvar1 = "value"
    <SPAN style="color:#00007F">Case</SPAN> 2
        myvar2 = "value"
        <SPAN style="color:#007F00">' ...</SPAN>
    <SPAN style="color:#00007F">Case</SPAN> 4
        myvar4 = "valut"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[zkaplan]

Function pullphrase(txt As String, srch1 As String, Optional srch2 As String, Optional srch3 As String, Optional srch4 As String, Optional srch5 As String) As String
for i = 1 to 5
if srchi = "" then exit for
next

i=i-1 'this excludes all blank variables from the code

For j = 1 to i
For z = 1 To Len(txt)
If Mid(txt, z, Len(srch=j)) = srchj Then Exit For
Next
If z < Len(txt) Then
pullphrase = srchj
next

The purpose of the function is to check if a number of different strings appear in a single string, and then return the first string that appears. There are other ways of approaching this without using the variable to construct another variable, but this is much shorter and simpler. It is also something I have come across before.

 

[Von Pookie]

Works for me:

Option Base 1
Function pullphrase(txt As String, srch1 As String, Optional srch2 As String, Optional srch3 As String, Optional srch4 As String, Optional srch5 As String) As String
Dim i As Integer, myVals As Variant
Dim j As Integer, z As Integer

myVals = Array(srch1, srch2, srch3, srch4, srch5)

For i = LBound(myVals) To UBound(myVals)
    If myVals(i) = "" Then Exit For
Next i

i = i - 1 'this excludes all blank variables from the code

For j = 1 To i
    For z = 1 To Len(txt)
        If Mid(txt, z, Len(myVals(j))) = myVals(j) Then Exit For
    Next z
    If z < Len(txt) Then
        pullphrase = myVals(j)
    End If
Next j

End Function

 

[Norie]

I don't see why you can't use an array here.

Just change the parameters for the function.

By the way would it not be easier to use Instr to find a string within a string.

Function pullphrase(t As String, strSrch()) As String
Dim I As Long
For I = LBound(strSrch) To UBound(strSrch)
    If InStr(t, strSrch(I)) > 1 Then Exit For
Next I

pullphrase = strSrch(I)

End Function

Sub test()
Dim x(2)
    x(1) = "the"
    x(2) = "this"
   MsgBox pullphrase(" the last time", x())
   
End Sub