|
|
Hello,
In the following function below, I am trying to select a range of cells
containing text, extract the numbers from those text strings, assign each
string to an array index, and finally return the array as a range. I haven't
used an array in a function before, so I'm sure my error is there. The
number-extracting part works fine (the "Digi" loop) as I have used it
elsewhere, but I think I am not assigning the strings to the array properly.
I appreciate any suggestions!
Code:
Public Function ExtractFundID(ByVal HoldingNameRange As Range) As Range
Dim HoldingName As Range
Dim IDOutputList As Range
Dim IDOutputArray() As Integer
Dim OutputIndex As Integer
Dim IDString As String
IDString = ""
OutputIndex = 0
For Each HoldingName In HoldingNameRange
For Digi = 1 To Len(HoldingName)
If IsNumeric(Mid(HoldingName, Digi, 1)) Then
Chara = Mid(HoldingName, Digi, 1)
Else: GoTo Loopy
End If
IDString = IDString & Chara
Loopy:
Next Digi
IDOutputArray(OutputIndex) = IDString
IDString = ""
OutputIndex = OutputIndex + 1
Next HoldingName
ReDim Preserve IDOutputArray(OutputIndex)
ExtractFundID.Name = "FundIDRange"
ExtractFundID.Value = IDOutputArray
End Function
|
|
Try this
Public Function ExtractFundID(ByVal HoldingNameRange As Range) As Variant
Dim HoldingName As Range
Dim IDOutputList As Range
Dim IDOutputArray() As Integer
Dim OutputIndex As Integer
Dim IDString As String
OutputIndex = 0
For Each HoldingName In HoldingNameRange
IDString = ""
For Digi = 1 To Len(HoldingName)
If IsNumeric(Mid(HoldingName, Digi, 1)) Then
Chara = Mid(HoldingName, Digi, 1)
IDString = IDString & Chara
End If
Next Digi
OutputIndex = OutputIndex + 1
redim preserve IDOutputArray(OutputIndex)
IDOutputArray(OutputIndex) = IDString
Next HoldingName
ExtractFundID = IDOutputArray
End Function
"FrankJO" wrote:
[Quoted Text] > Hello,
>
> In the following function below, I am trying to select a range of cells
> containing text, extract the numbers from those text strings, assign each
> string to an array index, and finally return the array as a range. I haven't
> used an array in a function before, so I'm sure my error is there. The
> number-extracting part works fine (the "Digi" loop) as I have used it
> elsewhere, but I think I am not assigning the strings to the array properly.
> I appreciate any suggestions!
>
> Code:
>
> Public Function ExtractFundID(ByVal HoldingNameRange As Range) As Range
> Dim HoldingName As Range
> Dim IDOutputList As Range
> Dim IDOutputArray() As Integer
> Dim OutputIndex As Integer
> Dim IDString As String
>
> IDString = ""
> OutputIndex = 0
>
> For Each HoldingName In HoldingNameRange
>
> For Digi = 1 To Len(HoldingName)
> If IsNumeric(Mid(HoldingName, Digi, 1)) Then
> Chara = Mid(HoldingName, Digi, 1)
> Else: GoTo Loopy
> End If
> IDString = IDString & Chara
>
>
> Loopy:
>
>
> Next Digi
> IDOutputArray(OutputIndex) = IDString
> IDString = ""
> OutputIndex = OutputIndex + 1
>
> Next HoldingName
>
> ReDim Preserve IDOutputArray(OutputIndex)
> ExtractFundID.Name = "FundIDRange"
> ExtractFundID.Value = IDOutputArray
>
>
>
> End Function
>
|
|
Thanks for the response, Joel (and the improvements to the code), but
unfortunately I am still getting a "#VALUE!" error when I apply it in a cell.
I have also tried entering the function with "Cntrl + Shift + Enter", just in
case. The range that I am selecting in the function contains variations of
"Fund:1234", so I expect the function to just return "1234" (or whichever
number) for each cell in the range. No luck yet..
"Joel" wrote:
[Quoted Text] > Try this
>
> Public Function ExtractFundID(ByVal HoldingNameRange As Range) As Variant
> Dim HoldingName As Range
> Dim IDOutputList As Range
> Dim IDOutputArray() As Integer
> Dim OutputIndex As Integer
> Dim IDString As String
>
> OutputIndex = 0
>
> For Each HoldingName In HoldingNameRange
> IDString = ""
> For Digi = 1 To Len(HoldingName)
> If IsNumeric(Mid(HoldingName, Digi, 1)) Then
> Chara = Mid(HoldingName, Digi, 1)
> IDString = IDString & Chara
> End If
>
> Next Digi
> OutputIndex = OutputIndex + 1
> redim preserve IDOutputArray(OutputIndex)
> IDOutputArray(OutputIndex) = IDString
>
> Next HoldingName
>
> ExtractFundID = IDOutputArray
>
>
> End Function
>
> "FrankJO" wrote:
>
> > Hello,
> >
> > In the following function below, I am trying to select a range of cells
> > containing text, extract the numbers from those text strings, assign each
> > string to an array index, and finally return the array as a range. I haven't
> > used an array in a function before, so I'm sure my error is there. The
> > number-extracting part works fine (the "Digi" loop) as I have used it
> > elsewhere, but I think I am not assigning the strings to the array properly.
> > I appreciate any suggestions!
> >
> > Code:
> >
> > Public Function ExtractFundID(ByVal HoldingNameRange As Range) As Range
> > Dim HoldingName As Range
> > Dim IDOutputList As Range
> > Dim IDOutputArray() As Integer
> > Dim OutputIndex As Integer
> > Dim IDString As String
> >
> > IDString = ""
> > OutputIndex = 0
> >
> > For Each HoldingName In HoldingNameRange
> >
> > For Digi = 1 To Len(HoldingName)
> > If IsNumeric(Mid(HoldingName, Digi, 1)) Then
> > Chara = Mid(HoldingName, Digi, 1)
> > Else: GoTo Loopy
> > End If
> > IDString = IDString & Chara
> >
> >
> > Loopy:
> >
> >
> > Next Digi
> > IDOutputArray(OutputIndex) = IDString
> > IDString = ""
> > OutputIndex = OutputIndex + 1
> >
> > Next HoldingName
> >
> > ReDim Preserve IDOutputArray(OutputIndex)
> > ExtractFundID.Name = "FundIDRange"
> > ExtractFundID.Value = IDOutputArray
> >
> >
> >
> > End Function
> >
|
|
When I debug functions I do the following
1) Insert break point on the function line in the VBA window by click with
mouse on the line and then pressing F9
2) go back to worksheet a forced a change to the worksheet. I usually do
this by click on the cell with the function call. then go to the F(x) box on
the top of the worksheet a press on the end of the statement. then press
Enter. Excel should stop on the 1st line of the function where the break
point is set.
3) On the step through the code using F8, or add more break points using F9
and pressing F5 to continue to next break point.
4) I also add watch points to the VBA window by highlighting differnt
variable with mouse. then right click the mouse and select ADD Watch and
press OK on pop up window.
"FrankJO" wrote:
[Quoted Text] > Thanks for the response, Joel (and the improvements to the code), but
> unfortunately I am still getting a "#VALUE!" error when I apply it in a cell.
> I have also tried entering the function with "Cntrl + Shift + Enter", just in
> case. The range that I am selecting in the function contains variations of
> "Fund:1234", so I expect the function to just return "1234" (or whichever
> number) for each cell in the range. No luck yet..
>
> "Joel" wrote:
>
> > Try this
> >
> > Public Function ExtractFundID(ByVal HoldingNameRange As Range) As Variant
> > Dim HoldingName As Range
> > Dim IDOutputList As Range
> > Dim IDOutputArray() As Integer
> > Dim OutputIndex As Integer
> > Dim IDString As String
> >
> > OutputIndex = 0
> >
> > For Each HoldingName In HoldingNameRange
> > IDString = ""
> > For Digi = 1 To Len(HoldingName)
> > If IsNumeric(Mid(HoldingName, Digi, 1)) Then
> > Chara = Mid(HoldingName, Digi, 1)
> > IDString = IDString & Chara
> > End If
> >
> > Next Digi
> > OutputIndex = OutputIndex + 1
> > redim preserve IDOutputArray(OutputIndex)
> > IDOutputArray(OutputIndex) = IDString
> >
> > Next HoldingName
> >
> > ExtractFundID = IDOutputArray
> >
> >
> > End Function
> >
> > "FrankJO" wrote:
> >
> > > Hello,
> > >
> > > In the following function below, I am trying to select a range of cells
> > > containing text, extract the numbers from those text strings, assign each
> > > string to an array index, and finally return the array as a range. I haven't
> > > used an array in a function before, so I'm sure my error is there. The
> > > number-extracting part works fine (the "Digi" loop) as I have used it
> > > elsewhere, but I think I am not assigning the strings to the array properly.
> > > I appreciate any suggestions!
> > >
> > > Code:
> > >
> > > Public Function ExtractFundID(ByVal HoldingNameRange As Range) As Range
> > > Dim HoldingName As Range
> > > Dim IDOutputList As Range
> > > Dim IDOutputArray() As Integer
> > > Dim OutputIndex As Integer
> > > Dim IDString As String
> > >
> > > IDString = ""
> > > OutputIndex = 0
> > >
> > > For Each HoldingName In HoldingNameRange
> > >
> > > For Digi = 1 To Len(HoldingName)
> > > If IsNumeric(Mid(HoldingName, Digi, 1)) Then
> > > Chara = Mid(HoldingName, Digi, 1)
> > > Else: GoTo Loopy
> > > End If
> > > IDString = IDString & Chara
> > >
> > >
> > > Loopy:
> > >
> > >
> > > Next Digi
> > > IDOutputArray(OutputIndex) = IDString
> > > IDString = ""
> > > OutputIndex = OutputIndex + 1
> > >
> > > Next HoldingName
> > >
> > > ReDim Preserve IDOutputArray(OutputIndex)
> > > ExtractFundID.Name = "FundIDRange"
> > > ExtractFundID.Value = IDOutputArray
> > >
> > >
> > >
> > > End Function
> > >
|
|
Update: I changed the code to what appears below, and I am no longer getting
a value error, but it is only returning the first item in the array, when it
should return all items in the array to a range of cells. Ideas?
Revised Code:
Public Function ExtractFundID(ByVal HoldingNameRange As Range) As Variant
Dim HoldingName As Range
Dim IDOutputList As Range
Dim IDOutputArray() As String
Dim OutputIndex As Integer
Dim IDString As String
OutputIndex = 0
For Each HoldingName In HoldingNameRange
IDString = ""
For Digi = 1 To Len(HoldingName)
If IsNumeric(Mid(HoldingName, Digi, 1)) Then
Chara = Mid(HoldingName, Digi, 1)
IDString = IDString & Chara
End If
Next Digi
OutputIndex = OutputIndex + 1
ReDim Preserve IDOutputArray(OutputIndex)
IDOutputArray(OutputIndex - 1) = IDString
Next HoldingName
ExtractFundID = IDOutputArray
End Function
|
|
It works, but you have to highlight the correct number of cells on the
worksheet to get it to work. Also use Shift+Control+Enter
"FrankJO" wrote:
[Quoted Text] > Update: I changed the code to what appears below, and I am no longer getting
> a value error, but it is only returning the first item in the array, when it
> should return all items in the array to a range of cells. Ideas?
>
> Revised Code:
>
> Public Function ExtractFundID(ByVal HoldingNameRange As Range) As Variant
> Dim HoldingName As Range
> Dim IDOutputList As Range
> Dim IDOutputArray() As String
> Dim OutputIndex As Integer
> Dim IDString As String
>
> OutputIndex = 0
>
> For Each HoldingName In HoldingNameRange
> IDString = ""
> For Digi = 1 To Len(HoldingName)
> If IsNumeric(Mid(HoldingName, Digi, 1)) Then
> Chara = Mid(HoldingName, Digi, 1)
> IDString = IDString & Chara
> End If
> Next Digi
>
> OutputIndex = OutputIndex + 1
> ReDim Preserve IDOutputArray(OutputIndex)
> IDOutputArray(OutputIndex - 1) = IDString
>
> Next HoldingName
>
>
> ExtractFundID = IDOutputArray
>
>
> End Function
>
>
>
>
|
|
Thanks, Joel! That did the trick.
"Joel" wrote:
[Quoted Text] > It works, but you have to highlight the correct number of cells on the
> worksheet to get it to work. Also use Shift+Control+Enter
>
> "FrankJO" wrote:
>
> > Update: I changed the code to what appears below, and I am no longer getting
> > a value error, but it is only returning the first item in the array, when it
> > should return all items in the array to a range of cells. Ideas?
> >
> > Revised Code:
> >
> > Public Function ExtractFundID(ByVal HoldingNameRange As Range) As Variant
> > Dim HoldingName As Range
> > Dim IDOutputList As Range
> > Dim IDOutputArray() As String
> > Dim OutputIndex As Integer
> > Dim IDString As String
> >
> > OutputIndex = 0
> >
> > For Each HoldingName In HoldingNameRange
> > IDString = ""
> > For Digi = 1 To Len(HoldingName)
> > If IsNumeric(Mid(HoldingName, Digi, 1)) Then
> > Chara = Mid(HoldingName, Digi, 1)
> > IDString = IDString & Chara
> > End If
> > Next Digi
> >
> > OutputIndex = OutputIndex + 1
> > ReDim Preserve IDOutputArray(OutputIndex)
> > IDOutputArray(OutputIndex - 1) = IDString
> >
> > Next HoldingName
> >
> >
> > ExtractFundID = IDOutputArray
> >
> >
> > End Function
> >
> >
> >
> >
|
|
|