splitting a cell into 2

[alldreans]

Hello,

I can't figure this one out. Via a web query, I get a
value for H2 of "1,926.09 - 1,954.62".

I'd like to, through VBA, split the value in H2 and
paste them into 2 cells, I2 and J2. The f'inal result
would be I2 = "1,926.09" and J2 = "1,954.62".

Can anyone help me?

Thanks in advance

 

[Frank Kabel]

Hi
why not use a non-VBA solution: Use 'Data - Text to
columns'

Or is VBA required for you?

 

[alldreams]

Hi Frank,

VBA would be ideal so I can have one macro do it all in
one click.

 

[Frank Kabel]

Hi
try (watch for linewraps):

Sub split_rows()
Dim RowNdx As Long
Dim LastRow As Long
Dim left_val
Dim right_val

Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
With Cells(RowNdx, "H")
If .Value <> "" Then
left_val = CDbl(Trim(Left(.Value, InStr
(.Value, "-") - 1)))
right_val = CDbl(Trim(Mid(.Value, InStr
(.Value, "-") + 1, 15)))
.Offset(0, 1).Value = left_val
.Offset(0, 2).Value = right_val
End If
End With
Next RowNdx
Application.ScreenUpdating = True
End Sub

 

[alldreams]

Hi Frank,

thanks a bunch!

-----Original Message-----
Hi
try (watch for linewraps):

Sub split_rows()
Dim RowNdx As Long
Dim LastRow As Long
Dim left_val
Dim right_val

Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "H").End (xlUp).Row
For RowNdx = LastRow To 1 Step -1
With Cells(RowNdx, "H")
If .Value <> "" Then
left_val = CDbl(Trim(Left(.Value, InStr
(.Value, "-") - 1)))
right_val = CDbl(Trim(Mid(.Value, InStr
(.Value, "-") + 1, 15)))
.Offset(0, 1).Value = left_val
.Offset(0, 2).Value = right_val
End If
End With
Next RowNdx
Application.ScreenUpdating = True
End Sub


.