Hi everyone
[Sorry originally posted this to the wrong group, duh and some kind person
put it in an even wronger group]
I have a sheet that uses a UDF to get the weight from a product string
[Code:]Function Wgt(rCell As Range) As Double
'***************************************************************
'*Extract a number from a cell containing text and numbers.
'*i.e. JSBBC120X006 TrayCount = 6
'***************************************************************
Dim iCount As Integer, i As Integer, iLoop As Integer
Dim sText As String
Dim lNum As String
Dim vVal
Dim Flag As Boolean
'get the Alphanumeric value of the cell
sText = rCell
'determine the number of characters in the cell
iLoop = Len(sText)
'Set up the counter to loop backwards through the length of the cell
For iCount = 1 To iLoop
'Put the value of the character determined by the counter into vVal
vVal = Mid(sText, iCount, 1)
'If vVal = "X" Then Flag = True
'If Flag = True Then _
'If vVal is a number add 1 to i and put the number into lNum
If IsNumeric(vVal) Then
i = i + 1
lNum = lNum & Mid(sText, iCount, 1)
End If
'End If
'If you have collected some numbers but now there is some text (ie "x") stop.
If i > 0 And IsNumeric(vVal) = False Then GoTo Finish
Next iCount
Finish:
'put the value of lNum into the cell
If lNum = "" Then lNum = 0
If lNum < 10 Then lNum = 0
Wgt = CDbl(lNum)
End Function
[/Code]
However I am now using powerpivot and I need to get this value into a new column with powerpivot, unless somone can suggest another way.
Any ideas
Martin
[Sorry originally posted this to the wrong group, duh and some kind person
put it in an even wronger group]I have a sheet that uses a UDF to get the weight from a product string
[Code:]Function Wgt(rCell As Range) As Double
'***************************************************************
'*Extract a number from a cell containing text and numbers.
'*i.e. JSBBC120X006 TrayCount = 6
'***************************************************************
Dim iCount As Integer, i As Integer, iLoop As Integer
Dim sText As String
Dim lNum As String
Dim vVal
Dim Flag As Boolean
'get the Alphanumeric value of the cell
sText = rCell
'determine the number of characters in the cell
iLoop = Len(sText)
'Set up the counter to loop backwards through the length of the cell
For iCount = 1 To iLoop
'Put the value of the character determined by the counter into vVal
vVal = Mid(sText, iCount, 1)
'If vVal = "X" Then Flag = True
'If Flag = True Then _
'If vVal is a number add 1 to i and put the number into lNum
If IsNumeric(vVal) Then
i = i + 1
lNum = lNum & Mid(sText, iCount, 1)
End If
'End If
'If you have collected some numbers but now there is some text (ie "x") stop.
If i > 0 And IsNumeric(vVal) = False Then GoTo Finish
Next iCount
Finish:
'put the value of lNum into the cell
If lNum = "" Then lNum = 0
If lNum < 10 Then lNum = 0
Wgt = CDbl(lNum)
End Function
[/Code]
However I am now using powerpivot and I need to get this value into a new column with powerpivot, unless somone can suggest another way.
Any ideas
Martin
