Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with file open and data organise macro
hi, I have got some code from a couple of friends. It uses the file dialo box to open separate text files into 1 excel in multiple worksheet THis is great. My data is in the following format in the file A 1 B 12 C 128 D 1233 A 1 B 2 C 18 D 133 I need the data in the worksheet to be arranged in the followin format: A B C D 1 12 128 1233 1 2 18 133 The data files are a couple of columns and the data names repeat alot. I would love to parse the data directly into my worksheets in the abov format style. I am inclduing the file open marco with test data files. am also includuing my own data.txt file with my macro which currentl organised the data the way I want. Does anybody else have any option to edit this marco to make it work the way i want Regards, Fran +------------------------------------------------------------------- |Filename: Sample Code Files.zip |Download: http://www.excelforum.com/attachment.php?postid=3598 +------------------------------------------------------------------- -- frankcas ----------------------------------------------------------------------- frankcase's Profile: http://www.excelforum.com/member.php...fo&userid=1951 View this thread: http://www.excelforum.com/showthread.php?threadid=38704 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with file open and data organise macro
Something like this might work for you:
Sub MoveData() Dim Rng As Range Dim Cell As Range Dim fndCell As Range Dim endRow As Long Dim endCol As Integer Dim thsSht As Worksheet Dim newSht As Worksheet Dim Hdr As String Dim Dta As String Set thsSht = ActiveSheet With thsSht endRow = .Cells(Rows.Count, 1).End(xlUp).Row Set Rng = Range(.Cells(1, 1), .Cells(endRow, 1)) End With Set newSht = Sheets.Add For Each Cell In Rng Hdr = Cell.Value Dta = Cell.Offset(0, 1).Value If newSht.Range("A1").Value = Empty Then newSht.Cells(1, 1).Value = Hdr newSht.Cells(2, 1).Value = Dta Else Set fndCell = newSht.Rows("1:1") _ .Find(Hdr, LookIn:=xlValues) If Not fndCell Is Nothing Then endRow = newSht.Cells _ (Rows.Count, fndCell.Column).End(xlUp).Row + 1 newSht.Cells(endRow, fndCell.Column).Value = Dta Else endCol = newSht.Cells(1, Columns.Count) _ .End(xlToLeft).Column + 1 newSht.Cells(1, endCol).Value = Hdr newSht.Cells(2, endCol).Value = Dta End If End If Next Cell End Sub Hope this helps Rowan "frankcase" wrote: hi, I have got some code from a couple of friends. It uses the file dialog box to open separate text files into 1 excel in multiple worksheet. THis is great. My data is in the following format in the file A 1 B 12 C 128 D 1233 A 1 B 2 C 18 D 133 I need the data in the worksheet to be arranged in the following format: A B C D 1 12 128 1233 1 2 18 133 The data files are a couple of columns and the data names repeat alot. I would love to parse the data directly into my worksheets in the above format style. I am inclduing the file open marco with test data files. I am also includuing my own data.txt file with my macro which currently organised the data the way I want. Does anybody else have any options to edit this marco to make it work the way i want Regards, Frank +-------------------------------------------------------------------+ |Filename: Sample Code Files.zip | |Download: http://www.excelforum.com/attachment.php?postid=3598 | +-------------------------------------------------------------------+ -- frankcase ------------------------------------------------------------------------ frankcase's Profile: http://www.excelforum.com/member.php...o&userid=19517 View this thread: http://www.excelforum.com/showthread...hreadid=387049 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open Excel file from VB and open MACRO | Excel Discussion (Misc queries) | |||
2007 Macro to Open File, Delete Contents, Save New File | Excel Discussion (Misc queries) | |||
how to group a row so i can organise data by diff fields | Excel Discussion (Misc queries) | |||
Automate open file, update links, run macro, close and save file | Excel Programming |