How could I optimize this VBA project ?

Hi !

I have a little issue with a workbook : it takes wayyyy too long to 
update...

So I have a sheet called "Map", where I must update comments on certain 
cells... the problem comes from the 2 For... Next statements that take a lot 
of time to process, because I have to activate 4 different sheets each 
time... I have about 150 system, and 12 planets for each, that's 1800 
possibilities !!!
Can I get the same result with another method ?

I tought about create another sheet where I could retrieve all infos I need, 
but I can't figure out how to do this...

Currently, I have :

For each system
    Computing coordinates => call the sheet "system"
        For planet = 1 to 12
            Retrieve the owner of the planet, and the alliance he is from => 
test with 2 sheets, "player" and "alliance"
            Retrieve if this alliance is friendly or not => table on another 
sheet, "diplo"
            if friendly color of the line will be blue, otherwise, red...
            keep in the memory the line and the shape
        Next planet
    Concatenate all the lines from the 12 planets, and copy this to the 
comment of range(coordinates)
    apply shape and colors to the comment
Next system

I can send the .xls file if you need the "true VBA" code ;)


0
gullick (4)
1/7/2006 4:10:28 PM
excel 39879 articles. 2 followers. Follow

0 Replies
389 Views

Similar Articles

[PageSpeed] 11

Reply:

Similar Artilces:

shareing resources within a project
I build 80+ homes a year, and need to manage it as one project-i think??? the thing is, i have for example 3 Plumbing contractors to do the rough-in for 15 homes, i can only give them 5 homes each and set Priorities for the homes. so oboviously home #2 can start until #1 is completed. and the same goes for plumbing finishes which have to be linked wt the plumber who did the rough-in. All of plumber's #1 tasks need to be linked not only for each unit but across all houses that plumber starts for me. for example plumber #1 may not be able to do the rough-in on 3 Apple St. becau...

Creating a chart via vba
Can anyone help with some example of how to create a chart via VBA? My problem is that the number of rows varies. Regards /Snedker There may not be any need for VBA. See the Excel | Tutorials | 'Dynamic Charts' page of my web site for options. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <r7ussv44bp0hhjh8tfqircph9nak5kp0hq@4ax.com>, morten@nospam_dbconsult.dk says... > Can anyone help with some example of how to create a chart via VBA? My > problem is ...

Restoring Microsoft Project PDF'd File
Is there a way to restore to the Microsof Project file format a Project file that has been converted to a PDF. Only have the PDF of teh schedule and would like to be able to manipulate the data in Project. Thanks, This is a multi-part message in MIME format. ------=_NextPart_000_001B_01CAAF41.E96D4D30 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Hi Fontaine, Welcome to this Microsoft Project newsgroup :) There's no way in Project to do this. However, I suppose you could try = copy/paste of the individual ...

OUTLOOK & VBA
We run Office 2003. We have the co name, reg no, directors, etc listed in everyone's signature clause. Should a director leave, I have to amend all the signature clauses and re-insert them at each workstation. Is there a way that I can have this done automatically via vba in Outlook or any other suggestions perhaps? Looked at forms but doesn't think that that will work the way I want it to. It must be part of the email message. VBA is the wrong solution for this. Since the signatures are nothing = more than .htm, .rtf, and .txt files stored in the user's Windows = profile folders...

Project Billing: Accounting for a Trade-In
I work with the Project module heavily and one issue that I am faced with is who to account for a customer trade-in during billing. Generally, a customer will order a piece of equipment, trading in the old one. On the final billing, I would then apply that trade-in to their bill. I have been able to work this in Sales Transactions by using the "Trade Discount" space and changing the posting account to what I need it to be. What I need to happen is when I take a trade-in I need to adjust the customers bill and also add that trade-in to my inventory. So then, when I sell it, I can ...

VBA macro guru help needed!
So here's the deal. Each month I have a workbook named RetailClientTemplate_YYMM.xls which contains sheets named by locale abbreviation (EN, ES, DE, IT, etc.). I have workbooks named RetailProjectLog_DATE.xlsx generated each day that are saved into a specific folder location. Each row on the first sheet in each RPL workbook has a locale in column A that matches the name of one of the sheets in the RCT workbook. What I need to do is make a macro that will copy & paste the rest of the data from each row in each of the RPL books (sheet(1), range B4:Q200, and col P data sep...

No function definitions in MFC project generated ActiveX control wrapper class
I have a dialog based MFC project created in Visual Studio 2003 environment. I inserted an ActiveX control object into a dialog and tried to created a variable for the control. But I don't see all the interface method definitions in the MFC generated wrapper class, so I cannot call those methods through the variable. I just added a new method to the control. I remembered it worked fine before. Does anyone have any idea why is that? Any help would be much appreciated! ...

Analyst time tracking on projects
Hello, Our company is currently using MSCRM v1.2 to track client interaction with our analysts. However, one feature that is missing from CRM v1.2 is the ability for our analysts to log how much time they spend on a particular client or porject. Is there a new feature in v3.0 that will allow this? If not is there any sort of third party module that is available that can do this? Thanks in Advance you could add a new entity to track time against or even use existing activities then have reports calculate time spent etc. There are many ways to achieve this on CRM 3 -- ========...

If then VBA
Hi Can I simplify the codes below, is it possible to use Select Case? Thanks. On Error Resume Next Range("B1:B500").Find("Product ").Select InvReqHeadingRowNo = ActiveCell.Row If Range("A1") = "" And Err <> 0 Then Msg = "This invoice request is incorrectly formatted." Msg = Msg & vbCrLf & "The title and products must be in cell A1 and column B respectively." Msg = Msg & vbCrLf & "Please ammend the invoice request before tranferring to template." ...

Vlookup with VBA
I have to do a vlookup with VBA in one worksheet in which the Table_Array will go from column A11 to L11 down to a row, containing a specific text, "Paid." is that possible Assuming that "paid" is in column L R = Application.Match("paid", Range("L11").resize(1000,0),0) + 10 Set LookupTable = Range("All","L" & R) X = Application.VLOOKUP("Jackson",LookupTable,11,0) On Wed, 13 Oct 2004 09:47:02 -0700, "Jeff" <Jeff@discussions.microsoft.com> wrote: >I have to do a vlookup with VBA in one wor...

Zip & Email
Hi, I need to zip a file that I am working on, then email automatically. I can do the emailing bit, but can't do the zip The OS is XP professional, with Office 2003 PS I know it can be done under WinZip, but I need the code for the OS default Zip Many thanks in advance John JohnUK shared this with us in microsoft.public.excel.misc: > Hi, I need to zip a file that I am working on, then email > automatically. I can do the emailing bit, but can't do > the zip > The OS is XP professional, with Office 2003 > PS I know it can be done under WinZip, but I need the > ...

MS Project Setup
I have a workstation setup document that spells out exactly how to: 1. Configure IE for both PMs and team members 2. Ensure that all the Windows components are installed in order to use Data Analysis, etc. 3. Ensure that Office and MS Project Pro are installed 4. Ensure that the right service pack and hotfixes are installed 5. Setup your Account in MS Project in order to connect to the database The document is illustrated with screen shots and uses hyperlinks when possible to make things easier. Some folks seem to believe that an "install program" can be written and ...

Scoring a test
I have a spreadsheet like this: Key | A | C | D | C | B| A | E | Name 1 | A | C | D | B | B | A | E | Name 2 | A | B | D | C | B | A | A | Name 3 | B | C | A | B | B | C | A | Going across in columns B - H (in this example) are each person's answers. The first row is the "key" to the test, i.e. the correct answers. I would like to calculate the number of correct answers in the last column for each person. I know this will work: =IF(B2=$B$1,1,0)+IF(C2=$C$1,1,0)+IF(D2=$D$1,1,)+..... This will compare each cell (answer) to the value in row 1 (key), return 1 if equal, t...

Excel 2003 - VBA
I am driving a sheet with macros and sometimes cells are manipulated using offset. This can get very convoluted and doesn't always work for me. The current issue is as follows: I would like to select a cell, then use the information in that cell and others in the same row. Once I have completed cell data manipulation, I would like to select the first 25 cells in that row and change the background color to indicate that this is new data. In VBA, how do I select a range when I do not know the row, I just work off activecell and offset. Thanks, Craig If you are working off the active...

VBA custom toolbar 12-28-09
In my system, I have an Excel 2003 custom toolbar saved in a file called Excel11.xlb. I have also created a custom toolbar in VBA. Can anyone tell me where it is saved. donwb ...

Great Plains Integration with Microsoft Project
Hi everyone, Does anyone know if Microsoft Great Plains can integrate with Microsoft Project? if you do, would you be able to point me in the correct direction to be able to do this. also what Integration Points their are and what information can be integrated. also if the integration is version specific which versions of each product would I require. Thanks This is a multi-part message in MIME format. ------=_NextPart_000_0025_01C5B84E.8CD471A0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: 7bit Hey Bill :-) As far as I know, there is cu...

Project 2007: weekend-only calendar?
As per the title, really! I have some tasks which only occur on weekends, and a 'weekend-only' team which will do them. It was simple to create a 'weekend only' calendar in Project 2003; is there a simple way of doing the same in 2007? Thanks Hi, Not as simple, but still: Tools Change Working time Select the resource Click Work Weeks Click details set Sat/Sun to "... specific working times", click enter before hitting OK Set Mo-Fri to "non working times" There you are. HTH -- Jan De Messemaeker Microsoft Project Most Valua...

Licensing Project Server 2007
I'm confused about what server licences are required for Project Server 2007. User/CAL licensing I understand, but the server licensing I'm not sure about. If I install PS2007 onto a 2 server configuration (WFE/App and DB), I need one PS2007 server licence. That's OK. If I add a separate WFE server, do I need to add another PS2007 server licence? The Project Server application will not change where it runs. I think I might need an additional licence. If I then choose to add an additional WFE/App server to the farm that is intended to provide resources for a separ...

Project/Sub-Project In Continuous List?
I've got a little timekeeping system that allows the user to enter task descriptions, start/stop times and then select "Project" from a combo box. I'd like to do some analysis on the data, and allocating tasks to sub-project's seems necessary. Problem is that in a continuous list, all the combo boxes behave the same. So if I make cboSubProject.RowSource tb a query that includes only sub-projects under the project selected in cboProject, the sub-projects in other rows on the screen come and go as cboSubProject's list changes. Anybody got a strategy for situation...

Date in VBA Problem ?
i have a problem ; how to solve that first let give you the table structure that i have i 1) [Sno] number 2) [item] Text 3) [Price] number 4) [Date] Date/time so i want to have a coding on a button on FORM such as Private Sub Command4_Click() With Form_Form1.Recordset ..MoveFirst While (Not .EOF) If (Form_Form1.item = "apple") Then Form_Form1.price = 100 End If ..MoveNext Wend End With End Sub so the above coding do work and give the desire result [Price] 100 for [tem] Apple but i want to how to change the item price for a specific date item to 200 or how to change ...

VBA Script stops working
What would cause my vba mods to just stop working after several months? I wrote a script to make the user-defined fields in Sales Transaction Entry required. Based it off of the sample script in the knowledgebase. Had to do a workaround because the transaction screen is an Alternate Window from Certitax. I set up the reference between the Certitax Alternate Sales Trx window and the GP User Defined window. Everything was working fine - for a while... One user that had the vba mod told me a couple weeks ago that her script stopped working. Since she wasn't doing sales order entry, ...

Help getting VBA to display a message
I would like the following code to display a message to the user if the click on the chart when no �End Flag� entries are FALSE. (I assume thi would be keyed to the "On Error" statement?) Private Sub Chart_Activate() Sheets("AgingPT").PivotTables("PivotTable3").PivotCache.Refresh On Error Resume Next Sheets("AgingPT").PivotTables("PivotTable3"). _ PivotFields("End Flag").CurrentPage = "FALSE" End Sub Can you help -- Message posted from http://www.ExcelForum.com ...

Calling the Page Setup PopUp from VBA..
Hello.. I am using Access 2003 and I have a button on my form that I will have the user click to bring up the Page Setup popup screen just like you would do if you chose File -> Page Setup. Does anyone have the VBA code to accomplish this? Please advise... Cheers! Brett Try, docmd.RunCommand acCmdPageSetup Also, do not forget to trap error 2501 should the user cancel the operation. -- Hope this helps, Daniel Pineault "Brett Davis" wrote: > Hello.. > > I am using Access 2003 and I have a button on my form that I will have the > user click to br...

Assignment of a VBA expression
Hi, I am buliding a form with multiple controls on it. I need to assign the same Event Procdure to a number of controls within hte form. This is what I have so far: Private Sub Text53_AfterUpdate() Dim ctlCurrentControl As Control Dim Value Set ctlCurrentControl = Screen.ActiveControl strControlName = ctlCurrentControl.Name Value = ctlCurrentControl Select Case Value Case 2.1 To 2.3 Me.Text65 = "2a" Case 3.1 To 3.3 Me.Text65 = "3a" End Select End Sub I would like to ...

Use of worksheet names in VBA
Hi , I am beginning to learn EXCEL Macros. I have defined a name XYZ = 10 in the workbokk. IS there a way to use this name in a VBA macro? One way: myVar = Evaluate(Names("XYZ").Value) In article <a50fe765.0410032203.446531da@posting.google.com>, Debu.mahapatra@unisys.com (Debu) wrote: > Hi , > > I am beginning to learn EXCEL Macros. I have defined a name XYZ = 10 > in the workbokk. IS there a way to use this name in a VBA macro? ...