Need help with macro

mchong

New Member
Joined
Sep 19, 2008
Messages
25
Guys,

Need help with a particulary thorny data table. And hoping someone here can give me a hand.

I've got data generated from our system that comes it a text file. This needs to be formatted into a report, so instead of doing this manually each time, we would like a macro to automate the process. Only problem here is that that the number of columns in the report always varies so I cannot get a recorded macro to work on this since the different number of columns just throws the macro off track.

How do I modify / write the macro so that regardless of changes to the number of columns, it will be able to select the correct ranges, add / delete columns in the right places?

If it helps, here's a sample of the code:-

Code:
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Rows("8:8").Select
Selection.AutoFilter
Range("A8").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$8:$BX$10000"), , xlYes).Name _
= "B13Table"
Columns("P:P").Select
Range("B13Table[[#Headers],[Movement Loading]]").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B13Table[[#Headers],[Column1]]").Select
ActiveCell.FormulaR1C1 = "DELIVERY"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""",RC[-2],RC[-1])"
Columns("S:S").Select
Range("B13Table[[#Headers],[Units(Qty.)]]").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B13Table[[#Headers],[Column1]]").Select
ActiveCell.FormulaR1C1 = "MODE"
Range("S9").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(B13Table[[#This Row],[Movement Loading]],""/"",B13Table[[#This Row],[Movement Discharge]])"
Range("S10").Select
Columns("X:X").Select
Range("B13Table[[#Headers],[CBM]]").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B13Table[[#Headers],[Column1]]").Select
ActiveCell.FormulaR1C1 = "OP"
Range("X9").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""CARRIER"",""COC"",""SOC"")"
Columns("AA:AA").Select
Range("B13Table[[#Headers],[Commodity]]").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B13Table[[#Headers],[Column1]]").Select
ActiveCell.FormulaR1C1 = "WT (TONNES)"
Range("AA9").Select
ActiveCell.FormulaR1C1 = "=B13Table[[#This Row],[Weight(Gross)]]/1000"
Range("AA10").Select
Columns("BX:BX").Select
Range("B13Table[[#Headers],[Charge Currency]]").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B13Table[[#Headers],[Column1]]").Select
ActiveCell.FormulaR1C1 = "CODE"
Range("BX9").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]=""OCF"",""FREIGHT"",IF(RC[-3]=""SFB"",""FREIGHT"",IF(RC[-3]=""SFC"",""FREIGHT"",IF(RC[-3]=""SWE"",""FREIGHT"",IF(RC[-3]=""BAF"",""BAF"","""")))))"
Range("BX10").Select
ActiveWindow.SmallScroll Down:=3
Range("B13Table[WT (TONNES)]").Select
Selection.NumberFormat = "#,##0.00"
Range("B13Table[[#Headers],[Container Type]]").Select
ActiveCell.FormulaR1C1 = "TYPE"
Range("B13Table[[#Headers],[Consigee]]").Select
ActiveCell.FormulaR1C1 = "CONSIGNEE"
Range("B13Table[[#Headers],[Units(Qty.)]]").Select
ActiveCell.FormulaR1C1 = "QTY"
Range("B13Table[[#Headers],[Doc Amount]]").Select
ActiveCell.FormulaR1C1 = "FRT"
End Sub

Thanks in advance!

Marco.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Joined
Jul 30, 2006
Messages
3,656
mchong,

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4.
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


And, please post all your macro code using code tags.

At the beginning of your posted code, enter the following without the quote marks:
["code"]


Your code goes here.


At the end of your posted code, enter the following without the quote marks:
["/code"]


Have a great day,
Stan
 

mchong

New Member
Joined
Sep 19, 2008
Messages
25
Stan,

THanks for the reply but unforunately can't seem to get exceljeanie to work. I get a message that says "more than 500cells inrange for forum" process abandoned. What do i need to do?

Marco.
 
Joined
Jul 30, 2006
Messages
3,656
mchong,

Try pasting smaller portions of your sheets.

Or:

See my Private Message to you (top right hand corner of MrExcel, Welcome, mchong.


Have a great day,
Stan
 

mchong

New Member
Joined
Sep 19, 2008
Messages
25
Thanks Stanley and all, have managed to solve this by looking around and fiddling on my own. Many thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,418
Messages
5,596,028
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top