Resource Issue with Excel

[reberryjr]

I'm in dire need of some help here. I'm getting the "Excel Ran out of Resources" error. I've googled a good bit, and it seems to have something to do with this formula.
=MAX(IF(INDIRECT("'"&C2&"'!D:D")=Summaries!C2,INDIRECT("'"&C2&"'!I:I")))

Right now, I have about 20 of these formulas on one sheet. I anticipate there being roughly 50 records on that sheet. I haven't the foggiest idea on where to start, to find a different formula to use, or route to go, to fix this issue. My application is essentially useless until I can fix this.

Any constructive help would be very much appreciated!

 

[offthelip]

There is another thing which I forgot to mention. The system was designed for multiuser, however I realised that this was going to create a problem when adding new clients. So I set up a system where when the Master workbook was opened it asked you whether you wanted to open the workbook as "Read Only" and this was the default. I then tested whether the workbook was read only or not when the "New Client" button was pressed and prevented the creation of a New Client unless the user specifically opened the Master with Write access. This Allowed Multiuser access because the Client workbooks were NOT readoly, so four or five people would have the Master Workbook open ( all readonly) and be able to update the data on thier clients workbooks, all of which would eventaully get updated back in the Master workbook. Excel itself prevented two users opening the Master workbook with write access, so on the occasions when two users wanted to add a client they just had to take it turns. ( and actually talk to each other !!)
Just like your client Access was a no no they wanted Excel, So I built a database using EXCEL

 

[reberryjr]

@offthelip , I need a little more help please. I have a form that is pulling data from one of the Client sheets, so that it can be updated. I was able to put this code together to access the correct sheet.

Set CS = Workbooks.Open("C:\Users\Rodger\Desktop\Bodies by Trish\Client Sheets\" & Me.txt_ClientID & ".xlsm")

When I got to submit updates, I'm getting a "method or data member not found" error. I'm thinking it's because I haven't declared what the target sheet is. I've tried a few variations based on what I've found on the web, but can't seem to piece together the correct code to activate a workbook that is already opened, where the workbook name is equal to a text box value.

 

[offthelip]

I virtually never define a variable as a workbook , a worksheet or a range. I much prefer to use variant arrays as much as possible. This means that I very rarely use the instruction format :
Set CS= ….. some object of workbook, worksheet , rangetype.
Although it is very much frowned upon by lots of the experts on these forums, I prefer to use activate and select to access workbooks and worksheets, the reason for this is it simplifies the syntax for correctly addressing workbooks, worksheets and ranges and helps in debugging. I acknowledge that it can be quicker to use the “With” construct, but since I already know how to write lightning fast code by using variant array all the time, the time taken for one or two activates and selects is negligible. I do totally accept that many people will use activate and select in a very inefficient way.
So the way I address lots of different sheets and workbooks is simple, I do it in a very similar way to a human, when I open a workbook , I remember it’s name, when I want to use a worksheet I select the worksheet. This has a great advantage when debugging the system because when you put a breakpoint in, when I swap to the EXCEL window, if I have activated a workbook and selected a worksheet, then that is what appears. If the wrong sheet appears I know the code is wrong. ( This is one of the reasons I prefer activate and select)
So the code I used moving between workbooks and worksheets is as follows:
At the top of the Master workbook I have the code line:

CurrentWorkbookName = ActiveWorkbook.Name

when opening the client workbook the code is:

      Workbooks.Open Filename:=newf
‘ This new workbook is now the active workbook so I save the workbook name
      NewWorkbookName = ActiveWorkbook.Name
      Worksheets("Financial").Select

I can then reference individual cells on this sheets simply:

Rown=Cells(2,2)

Similar with ranges

Range(“E1:E10”)=””

I then swap back to the Master workbook and a specific worksheet with this code

     Windows(CurrentWorkbookName).Activate
       Worksheets(monm).Select

Once again I can reference cells and ranges with the simplest of lines as above.
I find using these very simple methods, means I very rarely struggle with getting the correct syntax. Everytime I try using syntax that depends on setting some variable to a worksheet or workbook or even using the WITH construct I usually end up with some error. This is probably because I am a programmer who uses EXCEL rather than a EXCEL expert

 

[reberryjr]

I was actually able to solve the issue I was having with this little piece of code. I came into the office, swore at my machine and threatened it with a glass of water if it didn't cooperate. Once it did, I logged on here to say that I had fixed the issue, but hadn't seen that you replied.

[Set CS = Workbooks(Me.txt_ClientID.Text)/CODE]

I was thinking it had to be something more elaborate than what it is, so I was struggling with that.

I really appreciate the examples above, as they offer a different perspective which always proves useful.  For example, I know people that love VLOOKUPS, and swear by them.  I loathe them.  I'd rather nest a bunch of IF statements, b/c I've found they're more reliable.