Macro to extract in string

[Calop]

Any help with this is greatly appreciated.
Bob was graceous enough to provide the vba script below, however I now need
to make a minor change in it. It extracts part of a string in column A1 and
puts the results after the last row in col B. I have approx 200 rows that do
the same extraction. I would like to have the results placed in G1 thru
however many rows. Each extraction goes on same row as it's orginal. I know
to change the B to G for the column, but don't know what to change to have
the extracted part go to G1 and so on, down the rows.
Thank you, Calop
Sub AREA_REPORT()
Dim oCell As Range
Dim tmp As String
Dim iRow As Long
Dim sFirst As String

Set oCell = Selection.Find("*-*(*")
If Not oCell Is Nothing Then
iRow = iRow + 1
Cells(iRow, "B").Value = CheckData(oCell)
sFirst = oCell.Address
Do
Set oCell = Selection.FindNext(oCell)
If Not oCell Is Nothing Then
If oCell.Address <> sFirst Then
iRow = iRow + 1
Cells(iRow, "B").Value = CheckData(oCell)
End If
End If
Loop While Not oCell Is Nothing And oCell.Address <> sFirst
End If

End Sub

Private Function CheckData(cell As Range)
Dim iPos1 As Long
Dim iPos2 As Long

iPos1 = InStr(cell.Value, "-")
iPos1 = InStr(iPos1 + 1, cell.Value, "-")
iPos2 = InStr(iPos2 + 1, cell.Value, "(")
CheckData = Left(cell.Value, iPos1 - 1) & _
Right(cell.Value, Len(cell.Value) - iPos2 + 1)

End Function

 

[Bob Phillips]

Calop,

Is it not just a case of changing both instances of


Cells(iRow, "B").Value = CheckData(oCell)

to

Cells(iRow, "G").Value = CheckData(oCell)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Calop]

Bob,
I made the change you mentioned and it did put the data in G as I need,
however when the data is extracted from A1, it puts the extracted data in G
column as last entry. I need it in G1. It takes the extracted data from A2
and places it in G1.
Thanks,
Calop

 

[Bob Phillips]

Do you mean, extract A2 put in G1, A3 in G2, A4 in G5, etc. until column A
exhausted?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Calop]

That is what it does now. I need to have it extract from A1 and put in G1,
A2 in G2,etc. until column is exhausted. Thanks for your patience.
Calop