Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula
I receive a list daily of all new business', that are in a CSV format. I am trying to figure out the formula that "if AZ is in column E the sent the record Book 1, if AL is in column E then send the record t Book 2, etc" Basically I wish to place them in 4 separate books/sheets (for the time zones) Thanks in advance to all advic -- joetc ----------------------------------------------------------------------- joetcb's Profile: http://www.excelforum.com/member.php...fo&userid=3453 View this thread: http://www.excelforum.com/showthread.php?threadid=56332 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula
To clarify, you have several rows of data and you want each row sorted to
different Workbooks based on the contents of column E. Copy this code into the file and run. I think you may want to use another macro to import the CSV file and then run this code. Let me know if you don't know how to do this. Mike Option Explicit Sub SortToBooks() Dim TZ1 As Workbook, TZ2 As Workbook, TZ3 As Workbook, TZ4 As Workbook Dim cell As Range Application.ScreenUpdating = False Set TZ1 = Workbooks.Open("P:\Excel Help\Data Manipulation\Sort Rows To Different Books\Book1.xls") Set TZ2 = Workbooks.Open("P:\Excel Help\Data Manipulation\Sort Rows To Different Books\Book2.xls") Set TZ3 = Workbooks.Open("P:\Excel Help\Data Manipulation\Sort Rows To Different Books\Book3.xls") Set TZ4 = Workbooks.Open("P:\Excel Help\Data Manipulation\Sort Rows To Different Books\Book4.xls") For Each cell In UsedRange.EntireRow Select Case Cells(cell.Row, 5).Value Case "AL" cell.Copy Destination:=TZ1.Worksheets("Sheet1").Cells(TZ1.Sh eets("Sheet1").Cells(65536, 5).End(xlUp).Row + 1, 1) Case "AZ" cell.Copy Destination:=TZ2.Worksheets("Sheet1").Cells(TZ2.Sh eets("Sheet1").Cells(65536, 5).End(xlUp).Row + 1, 1) Case "FL" cell.Copy Destination:=TZ3.Worksheets("Sheet1").Cells(TZ3.Sh eets("Sheet1").Cells(65536, 5).End(xlUp).Row + 1, 1) Case "NM" cell.Copy Destination:=TZ4.Worksheets("Sheet1").Cells(TZ4.Sh eets("Sheet1").Cells(65536, 5).End(xlUp).Row + 1, 1) Case Else End Select Next cell TZ1.Save TZ2.Save TZ3.Save TZ4.Save TZ1.Close TZ2.Close TZ3.Close TZ4.Close Application.ScreenUpdating = True End Sub "joetcb" wrote: I receive a list daily of all new business', that are in a CSV format. I am trying to figure out the formula that "if AZ is in column E then sent the record Book 1, if AL is in column E then send the record to Book 2, etc" Basically I wish to place them in 4 separate books/sheets (for the 4 time zones) Thanks in advance to all advice -- joetcb ------------------------------------------------------------------------ joetcb's Profile: http://www.excelforum.com/member.php...o&userid=34534 View this thread: http://www.excelforum.com/showthread...hreadid=563326 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula
Had a chance to update the file so you can select the CSV file. Copy the
code below into a standard module of workbook (not one of the 4 you will be writing to). Change the file names to the files names of your files (you will need to include a path if they are not in the same location as the files the code is in). Change the AL, AZ, FL, NM to the appropriate matches that you are looking for in column E. Then run the macro. It will ask you for a file. Select the CSV file you want to distribute amongst the four files. If there is no match for the column E value then a inputbox will appear asking for an update. Test it out and let me know how it works. Mike Option Explicit Sub InputNDistrib() Dim TZ1 As Workbook, TZ2 As Workbook, TZ3 As Workbook, TZ4 As Workbook Dim cell As Range Dim Filename As String, ResultStr As String, TimeZone As String, NewZone As String Dim FileNum As Long, Counter As Long Dim sc As Integer, ec As Integer Filename = Application.GetOpenFilename Application.ScreenUpdating = False If Filename = "False" Then End Set TZ1 = Workbooks.Open("Book1.xls") Set TZ2 = Workbooks.Open("Book2.xls") Set TZ3 = Workbooks.Open("Book3.xls") Set TZ4 = Workbooks.Open("Book4.xls") Counter = 1 FileNum = FreeFile() Open Filename For Input As #FileNum Do While Seek(FileNum) <= LOF(FileNum) If EOF(FileNum) Then Exit Do Application.StatusBar = "Reading Line " & Counter & " of text file " & Filename Counter = Counter + 1 Line Input #FileNum, ResultStr checkagain: sc = InStr(InStr(InStr(InStr(1, ResultStr, ",") + 1, ResultStr, ",") + 1, ResultStr, ",") + 1, ResultStr, ",") + 1 ec = InStr(sc + 1, ResultStr, ",") TimeZone = Mid(ResultStr, sc, ec - sc) Select Case TimeZone Case "AL" TZ1.Worksheets("Sheet1").Cells(TZ1.Sheets("Sheet1" ).Cells(65536, 5).End(xlUp).Row + 1, 1) = ResultStr TZ1.Worksheets("Sheet1").Cells(TZ1.Sheets("Sheet1" ).Cells(65536, 5).End(xlUp).Row + 1, 1).TextToColumns _ DataType:=xlDelimited, Comma:=True Case "AZ" TZ2.Worksheets("Sheet1").Cells(TZ2.Sheets("Sheet1" ).Cells(65536, 5).End(xlUp).Row + 1, 1) = ResultStr TZ2.Worksheets("Sheet1").Cells(TZ2.Sheets("Sheet1" ).Cells(65536, 5).End(xlUp).Row + 1, 1).TextToColumns _ DataType:=xlDelimited, Comma:=True Case "FL" TZ3.Worksheets("Sheet1").Cells(TZ3.Sheets("Sheet1" ).Cells(65536, 5).End(xlUp).Row + 1, 1) = ResultStr TZ3.Worksheets("Sheet1").Cells(TZ3.Sheets("Sheet1" ).Cells(65536, 5).End(xlUp).Row + 1, 1).TextToColumns _ DataType:=xlDelimited, Comma:=True Case "NM" TZ4.Worksheets("Sheet1").Cells(TZ4.Sheets("Sheet1" ).Cells(65536, 5).End(xlUp).Row + 1, 1) = ResultStr TZ4.Worksheets("Sheet1").Cells(TZ4.Sheets("Sheet1" ).Cells(65536, 5).End(xlUp).Row + 1, 1).TextToColumns _ DataType:=xlDelimited, Comma:=True Case Else NewZone = InputBox("The string " & Chr(10) & Chr(10) & """" & ResultStr & """" & Chr(10) & Chr(10) & _ "does not contain a known timezone." & Chr(10) & Chr(10) & "Please enter the desired timezone.", "Timezone Error") ResultStr = Left(ResultStr, sc - 1) & NewZone & Mid(ResultStr, ec) GoTo checkagain End Select Loop Close TZ1.Save TZ2.Save TZ3.Save TZ4.Save TZ1.Close TZ2.Close TZ3.Close TZ4.Close Application.ScreenUpdating = True Application.StatusBar = False End Sub "joetcb" wrote: I receive a list daily of all new business', that are in a CSV format. I am trying to figure out the formula that "if AZ is in column E then sent the record Book 1, if AL is in column E then send the record to Book 2, etc" Basically I wish to place them in 4 separate books/sheets (for the 4 time zones) Thanks in advance to all advice -- joetcb ------------------------------------------------------------------------ joetcb's Profile: http://www.excelforum.com/member.php...o&userid=34534 View this thread: http://www.excelforum.com/showthread...hreadid=563326 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula | Excel Worksheet Functions | |||
copy formula down a column and have cell references change within formula | New Users to Excel | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |