Need help with Visual Basic

[Daniellel]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am completely self-taught with VB and am still a beginner. I have recorded a massive piece of code (it is about 11 pages in word) and it is really slow (about 30 mins) to run the macro. I does have a formula that it pastes into 840 cells on 4 different tabs ("=INDEX(TO3!$C$1:$I$9999,MATCH($A9&$B9,TO3!$A$1:$A$9999&TO3!$B$1:$B$9999,0),COLUMNS(TO3!$C2:C2))" ) I have posted about this before asking if anyone can help me speed it up but nothing has helped a great deal. The only thing people have referred to that I have not tried is a 'Helper Cell' but I am unsure what this means or how to do it. <o></o>
<o></o>
What I am really asking is if there is anyone out there that would be kind enough to run his or her expert eyes over it for me and to maybe help out. <o></o>
<o></o>
Can you help???<o></o>
<o></o>
Many thanks in advance<o></o>
<o></o>
Danielle

 

[Weaver]

Rather than post a formula that's difficult to understand without its accompanying dataset, it might be better if you could describe what you're trying to achieve in words, since it's possible that any speed gains might come from approaching the problem from a different angle. I hope this makes sense.

On the subject of 'helper cells', this usually means solving smaller parts of your formula in a different column in order to make the final formula simpler and therefore hopefully quicker. Again this would be easier to determine, for me at least, with a more verbose descrition of the issue and the task you're trying to accomplish.

 

[Weaver]

By the way, it looks like people are still responding to the other thread.

 

[Daniellel]

Rather than post a formula that's difficult to understand without its accompanying dataset, it might be better if you could describe what you're trying to achieve in words, since it's possible that any speed gains might come from approaching the problem from a different angle. I hope this makes sense.

On the subject of 'helper cells', this usually means solving smaller parts of your formula in a different column in order to make the final formula simpler and therefore hopefully quicker. Again this would be easier to determine, for me at least, with a more verbose descrition of the issue and the task you're trying to accomplish.

Thank you so much for your response. OK, so I am going to try to explain what I am trying to achieve as clearly as possible. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
The Objective:<o></o>
I have a system that downloads a list of all of my candidates and their personal information. I then have 4 separate reports to show when a candidate has self-scheduled into a telephone interview but the only think I have to identify them is the role they have applied for and their email address. The objective is to marry up the candidate details to the reports that show when they are booked form.<o></o>
<o></o>
The Current Solution:<o></o>
I am exporting all reports into my excel document and then have my macro copy all of the candidates email addresses, roles and interview start times into a new tab (one for each of the 4 reports). It then uses the formula "=INDEX(TO3!$C$1:$I$9999,MATCH($A9&$B9,TO3!$A$1:$A$9999&TO3!$B$1:$B$9999,0),COLUMNS(TO3!$C2:C2))" <o></o>
Selection.AutoFill Destination:=Range("E9:K9"), Type:=xlFillDefault<o></o>
Range("E9:K9").Select<o></o>
Selection.AutoFill Destination:=Range("E9:K30"), Type:=xlFillDefault<o></o>
Range("E9:K30").Select<o></o>
<o></o>
To pull all of the candidate details from the tab called TO3 (the one with all of the details). It then puts on a filter, deletes all of the blanks and does the same for all 4 new tabs. It finally copies and pastes special ‘Values only’ (to stop it reworking out all of the formulas again at the next step) copies again and puts into a nicely formatted overview page that shows everything from the 4 tabs.<o></o>
<o></o>
I really hope this makes sense, if you have any further questions, please let me know…

 

[Daniellel]

It seems that 'Weaver' has gone offline, can anyone else help me with this?

 

[p45cal]

Could you put the code on the intertweb somewhere, say box.net or some such?
Better if you could upload a version of your excel file with the code but with private/personal info changed/made up.

By the way, you say "I am exporting all reports into my excel document"; what format are these reports in before you process them?

 

[Daniellel]

Could you put the code on the intertweb somewhere, say box.net or some such?
Better if you could upload a version of your excel file with the code but with private/personal info changed/made up.

By the way, you say "I am exporting all reports into my excel document"; what format are these reports in before you process them?

Hi, Thanks for posting, i would put the code somewhere if there was anywhere that was free to use, do you know anywhere? Box.net charges! also i have a test document with test (made up) candidate details and emails addresses already to go. The documents i said i export are already in Excel, i just copy and paste them into my document.

 

[p45cal]

box.net is free
look here http://www.box.net/pricing/ under
Personal
Free

 

[Daniellel]

box.net is free
look here http://www.box.net/pricing/ under
Personal
Free

The pricing page goes straight to the sign up page and under personal it says $9.99????

 

[p45cal]

It's showing free for 5GB, 9.99 for 25GB here. Make sure you select only 5GB storage.

Are you saying the word Free does not show on the link I provided earlier?