Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro / VBA Help
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro / VBA Help
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" wrote in message ... 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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro / VBA Help
That worked like a charm, thank you so much :)
"Bernie Deitrick" wrote: 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" wrote in message ... 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro to copy and paste values (columns)I have a macro file built | Excel Programming | |||
AutoRun Macro with a delay to give user the choice to cancel the macro | Excel Programming | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |