Macro / VBA Help

[NewtoExpressionWeb]

Hello,
Excel 2007 Macro Help needed

I have a spreadsheet with a macro that imports a .CSV file, formats the
columns and does some search / replace. That part of the macro works fine.
The spreadsheet also has one column that contains text I would like to place
in other columns on the same row. Let me give an example.

In the example below, Column D is the column I would like to parse. It
contains a ton of text. Pieces of the text I would like in column B. Other
pieces in Column C. (For all rows in the sheet.) (For this simple example, I
would want to have the information between "abc" and "more text" appear in
ColumnB and the information After "more text" appear in ColumnC.

ColumnA ColumnB ColumnC ColumnD

Blah abc123 more text 999
Blah 2 abc456 more text 347

Would become...

ColumnA ColumnB ColumnC ColumnD

Blah 123 999 abc123 more text 999
Blah 2 456 347 abc456 more text 347

I know I could do this in Access with the Instr function, but I would prefer
an excel macro. I just cannot seem to get it right. (Oh, the number of rows
could be different every time)

Thanks,

 

[Bernie Deitrick]

Try the macro below, which will work on all of column D, starting in cell
D2.

If "abc " and " more text " can change cell to cell, then the code would
need to be modified....

HTH,
Bernie
MS Excel MVP

Sub ParseColD()
Dim myC As Range
Dim Str1 As String
Dim Str2 As String
Dim Start As Integer
Dim Finish As Integer

'Note extra spaces in the strings
Str1 = "abc "
Str2 = " more text "

For Each myC In Range("D2", Cells(Rows.Count, 4).End(xlUp))
Start = InStr(1, myC.Value, Str1) + Len(Str1)
Finish = InStr(1, myC.Value, Str2)
Cells(myC.Row, 2).Value = Val(Mid(myC.Value, Start, Finish - Start))
Cells(myC.Row, 3).Value = Val(Mid(myC.Value, Finish + Len(Str2),
Len(myC.Value)))
Next myC

End Sub

 

[NewtoExpressionWeb]

That worked like a charm, thank you so much

Try the macro below, which will work on all of column D, starting in cell
D2.

If "abc " and " more text " can change cell to cell, then the code would
need to be modified....

HTH,
Bernie
MS Excel MVP

Sub ParseColD()
Dim myC As Range
Dim Str1 As String
Dim Str2 As String
Dim Start As Integer
Dim Finish As Integer

'Note extra spaces in the strings
Str1 = "abc "
Str2 = " more text "

For Each myC In Range("D2", Cells(Rows.Count, 4).End(xlUp))
Start = InStr(1, myC.Value, Str1) + Len(Str1)
Finish = InStr(1, myC.Value, Str2)
Cells(myC.Row, 2).Value = Val(Mid(myC.Value, Start, Finish - Start))
Cells(myC.Row, 3).Value = Val(Mid(myC.Value, Finish + Len(Str2),
Len(myC.Value)))
Next myC

End Sub