Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array usage
Hi. I think I need to use an array to accomplish what I'm trying to d in an Excel file, but am not sure. Currently, I have an Excel workbook with several store numbers and whe I run a macro the sheet is sorted and each unique store number is copie and saved into a new workbook. This code is working fine, but I need t add some further functionality to it. Certain store numbers have sister stores that need to be copied int the parent workbook. For example, store 123 has no sister stores an is copied and saved into 123.xls. However, store 456 has sister stor 789 and file 456.xls is created with only store 456 data, but not 78 data. I need some code to say if current store is 456, then copy 45 AND 789 into 456.xls. There are about 500 stores of which only about have sister stores so I think creating a static array wouldn't be to bad. Any thoughts? Any help would be much appreciated. Thank -- brocklander ----------------------------------------------------------------------- brocklanders's Profile: http://www.excelforum.com/member.php...fo&userid=3520 View this thread: http://www.excelforum.com/showthread.php?threadid=54969 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array usage
Dear Brocklander,
I read the description of your problem 4 times over, but still can't figure out what you are trying to convey. Can you define input, processing and desired output in a structural way preferably accompanied by an example. This will make chances bigger that you get a response. Maybe put a relevant part of your code in as well. Good luck |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array usage
lexcel Some further explanation. I have 1 worksheet and only columns A and B contain data. The data in column A is store numbers and column B is territory number. Store number is a unique value so there are no duplicates. Territory number does have duplicates. Currently. when I run a macro I created, the sheet is sorted by Territory number. Then, all store numbers corresponding to that terrritory number are copied and saved into a new workbook and the workbook is saved as the territory number.xls. See below for example. Store Territory 1 101 2 101 3 101 4 101 5 102 6 103 7 105 8 105 Notice stores 1-4 have the same territory number. With my macro, stores 1-4 would be saved into a file called 101.xls. Only store 5 would be saved into 102.xls and so on. This code is working fine, but I need to add a new twist. Now, store 1 has a sister store in store 6. So, I need to add store 6 to 101.xls even though it's a different territory number. There's no real logic about which stores have sister stores, so I think it's something I'll have to hard code, which is fine because not many stores have sister stores. I apologize for giving the abridged version of my problem earlier. It probably just led to more confusion. Thanks again -- brocklanders ------------------------------------------------------------------------ brocklanders's Profile: http://www.excelforum.com/member.php...o&userid=35205 View this thread: http://www.excelforum.com/showthread...hreadid=549697 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array usage
Hi Mr Brocklander
The problem was indeed not as trivial as I thought at first. And I hope I understood this time what you want. If so, the code hereafter should do the job. If a sister store exists it is expected in column C, e.g. A B C 1 101 6 2 101 3 101 8 4 101 5 102 6 103 7 105 8 105 The records 6 103 and 8 105 will be added tot file 101.xls Option Explicit Const StoreCol = 1, TerCol = 2, SisCol = 3 Private CurrentSheet As Worksheet, NextRow As Long Sub BL() Dim Ro As Long, Cro As Long, Lastrow As Long Dim LookInCol As Long, Store As Long, SisterStore As Long Dim Terry As String, LookFor As String Dim ra As Range Application.ScreenUpdating = False ActiveSheet.Cells.Copy Worksheets.Add Cells.PasteSpecial xlPasteAll Application.CutCopyMode = False Do Ro = 1 Terry = Cells(Ro, TerCol) If Len(Terry) = 0 Then Exit Do Call NewFile(Terry) Do Call WriteRow(Ro) SisterStore = Cells(Ro, SisCol) Rows(Ro).Delete LookInCol = TerCol LookFor = Terry If SisterStore Then If WorksheetFunction.CountIf(Columns(StoreCol), SisterStore) Then LookFor = SisterStore LookInCol = StoreCol End If End If Set ra = Columns(LookInCol).Find(LookFor, LookIn:=xlValues, lookat:=xlWhole) If ra Is Nothing Then Exit Do Ro = ra.Row Loop Call CloseFile Loop With Application .DisplayAlerts = False ActiveSheet.Delete .DisplayAlerts = True .ScreenUpdating = True End With End Sub Sub NewFile(FileName As String) Dim s As Worksheet Set s = ActiveSheet Set CurrentSheet = Worksheets.Add CurrentSheet.name = FileName NextRow = 1 s.Activate End Sub Sub WriteRow(r As Long) Dim s As Worksheet CurrentSheet.Rows(NextRow).Value = Rows(r).Value NextRow = NextRow + 1 End Sub Sub CloseFile() Dim FilNam As String With CurrentSheet FilNam = .name .Columns(SisCol).Delete xlShiftToRight ' remove the Sister Store column from the result .Move ' to a new workbook End With ActiveWorkbook.SaveAs FilNam ActiveWorkbook.Close End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Increase PF Usage | Excel Worksheet Functions | |||
Can anyone shorten this one. (not overly clear on array usage) | Excel Worksheet Functions | |||
100% cpu usage | Excel Discussion (Misc queries) | |||
Getting my VB custom functions to support usage within an Array Formula - How do I ge | Excel Programming | |||
SQL - TOP 1 Usage | Excel Programming |