Getting data with web query and rearrange the data

[mrprofit]

Getting data with web query and rearrange the data (http://www.ozgrid.com/forum/showthread.php?t=199099)

thanks for any help on this,

when i enter the ticker symbol to A2:A100, the vba code to get the data and list them to B2:F100

[mrmmickle1]

This code will work....

Please note that the IsNumber Function can be credited to Rick Rothstein.


Sub Test()

Dim rng As Range
Dim cll As Range
Dim IntLp As Integer
Dim strt As Integer

Set rng = Sheets(1).Range("A1:M29")

For Each cll In rng
CllLen = Len(cll)
If CllLen > 0 Then
For IntLp = 1 To CllLen
strt = strt + 1
If IsNumber(Mid(cll, strt, IntLp)) <> True Then
cll.Value = Replace(cll.Value, Mid(cll, strt, IntLp), "")
End If
strt = 0
Next IntLp
End If
Next cll

End Sub

Function IsNumber(ByVal Value As String) As Boolean
' Uncomment the next statement out if you
' want to provide for plus/minus signs
' If Value Like "[+-]*" Then Value = Mid$(Value, 2)
IsNumber = Not Value Like "*[!0-9.]*" And _
Not Value Like "*.*.*" And _
Len(Value) > 0 And Value <> "." And _
Value <> vbNullString
End Function