Macro Help with formula combo

[jb09]

I am having trouble with my macro which is fairly simple. It is opening a book, formatting it, and then attempting to extract the information needed from a line of text. The text to run the process on will be D9:D250. Just playing with the sheet I was able to in other cells extract the data I want, but I am having trouble in the macro combining all of those steps into one and have them work. I am attempting to not have to use Cells O,P,Q and would just like the function to run and replace cell D9 with "T" and so on as the data goes.


Cell D9: " A T & T INC (NEW) C:00206R102 # 373177 T "

Cell 09: =FIND("#",D9,1) This gives the numerical value 32
Cell P9: =FIND(" ",MID(D9,O9+2,32),1) This gives the numerical value 7
Cell Q9: =MID(D9,O9+P9+2,10) This isolates the text I need and returns "T"


Currently this is the macro I have up until the point of my failure:

Sub Macro1()
'
' Macro1 Macro
'


'
    Workbooks.OpenText Filename:="B:\B\B\B.PRN", Origin:=437, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=True, OtherChar:="""", FieldInfo:=Array(Array(1, _
        2), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1) _
        , Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1)), _
        TrailingMinusNumbers:=True
    Application.Run "ConnectChartEvents"
    Columns("B:L").Select
    Columns("B:L").EntireColumn.AutoFit
End Sub
[endcode]

 

[Rijnsent]

Hi jb09,

I filled your formulae into a spreadsheet and switched macro recording on, the resulting code:

Range("O9").FormulaR1C1 = "=FIND(""#"",RC[-11],1)"
Range("P9").FormulaR1C1 = "=FIND("" "",MID(RC[-12],RC[-1]+2,32),1)"
Range("Q9").FormulaR1C1 = "=MID(RC[-13],RC[-2]+RC[-1]+2,10)"

And aggregated into 1 cell (the row below):

Range("Q10").FormulaR1C1 = "=MID(RC[-13],FIND(""#"",R[-1]C[-13],1)+FIND("" "",MID(RC[-13],FIND(""#"",R[-1]C[-13],1)+2,32),1)+2,10)"

As you can see, the references are relative, not absolute (to e.g. column D).

What I then would do:
-load the file
-fill the formula into your column Q (so that would be Range("Q2:Q100").FormulaR1C1 = etc.
-Calculate the sheet
-copy "Q2:Q100" and paste in "D2:D100" as values
-clean "Q2:Q100"

Hope that helps,

Koen

 

[jb09]

Thanks, that should work perfect. I was looking for something which I dont think was feasible, really appreciate the help