Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Array
This little program reads some date from one row on a sheet in a
workbook, then goes to another wb and inserts a sheet and pastes the data then goes back to the first wb/sheet and loops till it's out of data. I know all the data could be read at once and then all the pasting done with a dynamic array but I haven't been able to make that work. "DataTable.xls" will be a new file each day hence the need for a dynamic array to read the data. Any help would be greatly appreciated. Public Sub RunReports() Dim vManagers As Range, vManager As Range Dim DataArray(5) Windows("DataTable.xls").Activate On Error Resume Next Set vManagers = Range("D:D") On Error GoTo 0 If Not vManagers Is Nothing Then For Each vManager In vManagers If vManager.Value = "S. O' Neil" Then vManager.Activate DataArray(1) = ActiveCell.Offset(0, -3) 'Date DataArray(2) = ActiveCell.Offset(0, -1) 'Borrower Name DataArray(3) = ActiveCell.Offset(0, 1) 'Retail/ Broker DataArray(4) = ActiveCell.Offset(0, 3) 'Loan Number DataArray(5) = ActiveCell.Offset(0, 7) 'Amount Windows("Check Deposit Report.xls").Activate Sheets("CHECK-DEPOSIT").Copy After:=Sheets(1) ActiveSheet.Unprotect Range("F43").Value = DataArray(1) Range("B43").Value = DataArray(2) If DataArray(3) = "Broker" Then Range("F32").Value = DataArray(3) Else Range("F32").Value = "Retail" End If Range("A43").Value = DataArray(4) Range("I27").Value = -DataArray(5) Windows("DataTable.xls").Activate End If Next End If Windows("Check Deposit Report.xls").Activate End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Array
I would just loop through the range and avoid the array altogether.
Option Explicit Public Sub RunReports() Dim vManagers As Range Dim vManager As Range Dim TempWks As Worksheet With Workbooks("datatable.xls").Worksheets("Somesheetna mehere") 'headers in row 1???? Set vManagers = .Range("D2", .Cells(.Rows.Count, "D").End(xlUp)) End With For Each vManager In vManagers.Cells With Workbooks("check deposit report.xls") .Worksheets("Check-Deposit").Copy _ befo=.Sheets(1) End With Set TempWks = ActiveSheet With TempWks .Unprotect .Range("F43").Value = vManager.Offset(0, -3).Value 'date .Range("b43").Value = vManager.Offset(0, -1).Value 'borrower If LCase(vManager.Offset(0, 1).Value) = "broker" Then .Range("f32").Value = "Broker" Else .Range("F32").Value = "Retail" End If .Range("a43").Value = vManager.Offset(0, 3).Value 'Loan Number .Range("i27").Value = vManager.Offset(0, 7).Value 'Amount 'protect?? .Protect End With Next vManager End Sub You wanted a different worksheet for each cell in column D, right? John Pierce wrote: This little program reads some date from one row on a sheet in a workbook, then goes to another wb and inserts a sheet and pastes the data then goes back to the first wb/sheet and loops till it's out of data. I know all the data could be read at once and then all the pasting done with a dynamic array but I haven't been able to make that work. "DataTable.xls" will be a new file each day hence the need for a dynamic array to read the data. Any help would be greatly appreciated. Public Sub RunReports() Dim vManagers As Range, vManager As Range Dim DataArray(5) Windows("DataTable.xls").Activate On Error Resume Next Set vManagers = Range("D:D") On Error GoTo 0 If Not vManagers Is Nothing Then For Each vManager In vManagers If vManager.Value = "S. O' Neil" Then vManager.Activate DataArray(1) = ActiveCell.Offset(0, -3) 'Date DataArray(2) = ActiveCell.Offset(0, -1) 'Borrower Name DataArray(3) = ActiveCell.Offset(0, 1) 'Retail/ Broker DataArray(4) = ActiveCell.Offset(0, 3) 'Loan Number DataArray(5) = ActiveCell.Offset(0, 7) 'Amount Windows("Check Deposit Report.xls").Activate Sheets("CHECK-DEPOSIT").Copy After:=Sheets(1) ActiveSheet.Unprotect Range("F43").Value = DataArray(1) Range("B43").Value = DataArray(2) If DataArray(3) = "Broker" Then Range("F32").Value = DataArray(3) Else Range("F32").Value = "Retail" End If Range("A43").Value = DataArray(4) Range("I27").Value = -DataArray(5) Windows("DataTable.xls").Activate End If Next End If Windows("Check Deposit Report.xls").Activate End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Array
I'm back. That was weird. Therre was this intense light and then . , ,
nothing, till three weeks later. So where was I? Dave, I tried your code. It ran and ran and ran, hundreds of pages when there should have been only about 70 or so. Also, it picked up all the managers instead of just S. O'Neil. I would really like to use a dynamic array to do this because I would like to learn to work with them. Could this program be converted to that? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Array
Dave, I tried your code. It ran and ran and ran, hundreds of pages
when there should have been only about 70 or so. Also, it picked up all the managers instead of just S. O'Neil. I would really like to use a dynamic array to do this because I would like to learn to work with them. Could this program be converted to that? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Array
It creates a new sheet for every cell in that range. Maybe you have formulas
that evaluate to ="". So that they're getting sheets created, too??? For Each vManager In vManagers.Cells if trim(vmanager.value) = "" then 'do nothing else With Workbooks("check deposit report.xls") .Worksheets("Check-Deposit").Copy _ befo=.Sheets(1) End With Set TempWks = ActiveSheet With TempWks .Unprotect .Range("F43").Value = vManager.Offset(0, -3).Value 'date .Range("b43").Value = vManager.Offset(0, -1).Value 'borrower If LCase(vManager.Offset(0, 1).Value) = "broker" Then .Range("f32").Value = "Broker" Else .Range("F32").Value = "Retail" End If .Range("a43").Value = vManager.Offset(0, 3).Value 'Loan Number .Range("i27").Value = vManager.Offset(0, 7).Value 'Amount 'protect?? .Protect End With end if Next vManager John Pierce wrote: I'm back. That was weird. Therre was this intense light and then . , , nothing, till three weeks later. So where was I? Dave, I tried your code. It ran and ran and ran, hundreds of pages when there should have been only about 70 or so. Also, it picked up all the managers instead of just S. O'Neil. I would really like to use a dynamic array to do this because I would like to learn to work with them. Could this program be converted to that? -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Array
I thought that you wanted all the cells in that column to have a sheet created.
If you want just that single manager... For Each vManager In vManagers.Cells if lcase(vmanager.value) < lcase("S. O'Neil") then 'do nothing else With Workbooks("check deposit report.xls") .Worksheets("Check-Deposit").Copy _ befo=.Sheets(1) End With Set TempWks = ActiveSheet With TempWks .Unprotect .Range("F43").Value = vManager.Offset(0, -3).Value 'date .Range("b43").Value = vManager.Offset(0, -1).Value 'borrower If LCase(vManager.Offset(0, 1).Value) = "broker" Then .Range("f32").Value = "Broker" Else .Range("F32").Value = "Retail" End If .Range("a43").Value = vManager.Offset(0, 3).Value 'Loan Number .Range("i27").Value = vManager.Offset(0, 7).Value 'Amount 'protect?? .Protect End With end if Next vManager John Pierce wrote: Dave, I tried your code. It ran and ran and ran, hundreds of pages when there should have been only about 70 or so. Also, it picked up all the managers instead of just S. O'Neil. I would really like to use a dynamic array to do this because I would like to learn to work with them. Could this program be converted to that? -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Array
What goes where it says " 'do nothing"?
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Array
Nothing.
I used it as a comment to show that if the name is different from what you want, then the program doesn't do anything. John Pierce wrote: What goes where it says " 'do nothing"? -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Array
Dave, Thanks for hangin in there. It works but there's a problem.
After running 56 iterations it stops with Run-time error '1004' Copy method of Worksheet class failed. The earlier version did exactly the same thing which is why I was trying to change it somehow. I am running it on machines with Windows XP and Excel 2003. When I try to run it on a new computer with Vista and Excel 2007 I get an error at LCase, Compile error, Can't find project or library. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Array
I figured out the Can't find project or library error. The file had a
reference to Personal.xls but the new computer is so new I haven't even started my macro book. I simply deleted the reference from the file and the code ran fine. I tested it with 146 iterations. Unfortunately, I have to run it on machines at work that have XP/2003 and where it will only run 56 times. The problem appears to be the Excel version rather than the program, don't ya think? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Array
I don't think I've ever created this many new worksheets based on copying an
existing sheet. If I recall correctly, you can close the workbook (and excel???) after creating several worksheets (before it blows up), then reopen and continue where you left off. I _think_ that another workaround may be to use a worksheet in a different workbook as the template worksheet. I _think_ that people have said that stops the problem. John Pierce wrote: I figured out the Can't find project or library error. The file had a reference to Personal.xls but the new computer is so new I haven't even started my macro book. I simply deleted the reference from the file and the code ran fine. I tested it with 146 iterations. Unfortunately, I have to run it on machines at work that have XP/2003 and where it will only run 56 times. The problem appears to be the Excel version rather than the program, don't ya think? -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Array
So you've heard of this limit on the number of times a sheet can be
copied? Any idea why 56? I don't know why I hadn't thought of it sooner but I finally tried to manually copy the template sheet after running the program and it simply would not allow me to add any more copied sheets beyond the 57 already there which is consistent with the program stopping but I could insert a new blank sheet and I could apparently insert as many as I wanted (I tried up to 16). So it must be something to do with the Copy method in Excel 2003 is what I am led to conclude. On another topic, I'm using some code that I think you posted for generating a tool bar at run time that closes when the workbook is closed. I was wondering if such a tool bar could be further restricted to appear only on one specific worksheet of one specific workbook. Currently it appears in all open Excel workbooks and sheets until the host file is closed. It has very specific tools that only work on one sheet. It isn't really a problem as it is, just a matter of elegance. Thanks |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Array
I _think_ I've read posts with problems with inserting sheets in xl97. But I'm
not sure if it applies to xl2003. Maybe you can google for some hints. And you could use the workbook_deactivate (under ThisWorkbook) and the worksheet_deactivate (under the worksheet you want) to delete the toolbar. And you could use the worksheet_activate (under the worksheet) to rebuild it. The workbook_activate event (under ThisWorkbook) will have to be smart enough to check the activesheet name to see if the toolbar should be recreated. Under ThisWorkbook: Option Explicit Private Sub Workbook_Activate() If LCase(Me.ActiveSheet.Name) = LCase("Sheet1") Then Call CreateMenu End If End Sub Private Sub Workbook_Deactivate() Call DeleteMenu End Sub Under the sheet that needs the toolbar: Option Explicit Private Sub Worksheet_Activate() Call CreateMenu End Sub Private Sub Worksheet_Deactivate() Call DeleteMenu End Sub You could hide the toolbar if you wanted. But then the users could use tools|customize to show it whenever they want. (I didn't test the code or compile it, either.) John Pierce wrote: So you've heard of this limit on the number of times a sheet can be copied? Any idea why 56? I don't know why I hadn't thought of it sooner but I finally tried to manually copy the template sheet after running the program and it simply would not allow me to add any more copied sheets beyond the 57 already there which is consistent with the program stopping but I could insert a new blank sheet and I could apparently insert as many as I wanted (I tried up to 16). So it must be something to do with the Copy method in Excel 2003 is what I am led to conclude. On another topic, I'm using some code that I think you posted for generating a tool bar at run time that closes when the workbook is closed. I was wondering if such a tool bar could be further restricted to appear only on one specific worksheet of one specific workbook. Currently it appears in all open Excel workbooks and sheets until the host file is closed. It has very specific tools that only work on one sheet. It isn't really a problem as it is, just a matter of elegance. Thanks -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Array
Hi. That Excel 97 kb article is no longer around (afaik), but this
article happens to refer to that problem you are thinking of... http://support.microsoft.com/kb/236299 - - - Dana DeLouis Dave Peterson wrote: I _think_ I've read posts with problems with inserting sheets in xl97. But I'm not sure if it applies to xl2003. Maybe you can google for some hints. And you could use the workbook_deactivate (under ThisWorkbook) and the worksheet_deactivate (under the worksheet you want) to delete the toolbar. And you could use the worksheet_activate (under the worksheet) to rebuild it. The workbook_activate event (under ThisWorkbook) will have to be smart enough to check the activesheet name to see if the toolbar should be recreated. Under ThisWorkbook: Option Explicit Private Sub Workbook_Activate() If LCase(Me.ActiveSheet.Name) = LCase("Sheet1") Then Call CreateMenu End If End Sub Private Sub Workbook_Deactivate() Call DeleteMenu End Sub Under the sheet that needs the toolbar: Option Explicit Private Sub Worksheet_Activate() Call CreateMenu End Sub Private Sub Worksheet_Deactivate() Call DeleteMenu End Sub You could hide the toolbar if you wanted. But then the users could use tools|customize to show it whenever they want. (I didn't test the code or compile it, either.) John Pierce wrote: So you've heard of this limit on the number of times a sheet can be copied? Any idea why 56? I don't know why I hadn't thought of it sooner but I finally tried to manually copy the template sheet after running the program and it simply would not allow me to add any more copied sheets beyond the 57 already there which is consistent with the program stopping but I could insert a new blank sheet and I could apparently insert as many as I wanted (I tried up to 16). So it must be something to do with the Copy method in Excel 2003 is what I am led to conclude. On another topic, I'm using some code that I think you posted for generating a tool bar at run time that closes when the workbook is closed. I was wondering if such a tool bar could be further restricted to appear only on one specific worksheet of one specific workbook. Currently it appears in all open Excel workbooks and sheets until the host file is closed. It has very specific tools that only work on one sheet. It isn't really a problem as it is, just a matter of elegance. Thanks |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Array
That's the one I was thinking of...
Thanks Dana. Dana DeLouis wrote: Hi. That Excel 97 kb article is no longer around (afaik), but this article happens to refer to that problem you are thinking of... http://support.microsoft.com/kb/236299 - - - Dana DeLouis Dave Peterson wrote: I _think_ I've read posts with problems with inserting sheets in xl97. But I'm not sure if it applies to xl2003. Maybe you can google for some hints. And you could use the workbook_deactivate (under ThisWorkbook) and the worksheet_deactivate (under the worksheet you want) to delete the toolbar. And you could use the worksheet_activate (under the worksheet) to rebuild it. The workbook_activate event (under ThisWorkbook) will have to be smart enough to check the activesheet name to see if the toolbar should be recreated. Under ThisWorkbook: Option Explicit Private Sub Workbook_Activate() If LCase(Me.ActiveSheet.Name) = LCase("Sheet1") Then Call CreateMenu End If End Sub Private Sub Workbook_Deactivate() Call DeleteMenu End Sub Under the sheet that needs the toolbar: Option Explicit Private Sub Worksheet_Activate() Call CreateMenu End Sub Private Sub Worksheet_Deactivate() Call DeleteMenu End Sub You could hide the toolbar if you wanted. But then the users could use tools|customize to show it whenever they want. (I didn't test the code or compile it, either.) John Pierce wrote: So you've heard of this limit on the number of times a sheet can be copied? Any idea why 56? I don't know why I hadn't thought of it sooner but I finally tried to manually copy the template sheet after running the program and it simply would not allow me to add any more copied sheets beyond the 57 already there which is consistent with the program stopping but I could insert a new blank sheet and I could apparently insert as many as I wanted (I tried up to 16). So it must be something to do with the Copy method in Excel 2003 is what I am led to conclude. On another topic, I'm using some code that I think you posted for generating a tool bar at run time that closes when the workbook is closed. I was wondering if such a tool bar could be further restricted to appear only on one specific worksheet of one specific workbook. Currently it appears in all open Excel workbooks and sheets until the host file is closed. It has very specific tools that only work on one sheet. It isn't really a problem as it is, just a matter of elegance. Thanks -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Speed of fixed array versus dynamic array | Excel Programming | |||
Dynamic Array Lbound not working when only one value in array | Excel Programming | |||
Dynamic array | Excel Programming | |||
Dynamic 2D Array | Excel Programming | |||
Dynamic Array | Excel Programming |