# Posts by Sicarii

• ## Causes Of Formulas Recalculating

Re: Causes Of Workbook Recalculating

Taken from the site I posted for you:

Quote

The time taken by Excel to work out the calculation sequence can be increased by heavy use of functions such as INDIRECT and OFFSET which increase the complexity and volatility of the dependency tree.

Just to note as well, the following link is on that site, if you want to get really confused or enlightened about the calculation process:
http://msdn.microsoft.com/en-us/library/aa140058.aspx

• ## Summer Olympics

Re: Summer Olympics

I guess I should've deleted the politics part; it was an email floating around at work and since jhendersons been slacking on the funnies I figured I'd post this up.

I honestly did not watch any of the Olympics. I'd much rather to always watch Science, History, Discovery, etc...until my wife takes the remote!!!

• ## Excel 4.0 Macro Warning On Open

Re: &quot;excel 4.0 Macro&quot; Warning

Don't know if this helps or not...

Excel 4.0 Macro Reference

• ## Summer Olympics

Time now for the political comments to begin! But first! Here are the top nine comments made by NBC sports commentators so far during the Summer Olympics that they would like to take back:

1. Weightlifting commentator: 'This is Gregoriava from Bulgaria . I saw her snatch this morning during her warm up and it was amazing.'

2. Dressage commentator: 'This is really a lovely horse and I speak from personal experience since I once mounted her mother.'

3. Paul Hamm, Gymnast: 'I owe a lot to my parents, especially my mother and father.'

4. Boxing Analyst: 'Sure there have been injuries, and even some deaths in boxing, but none of them really that serious.'

5. Softball announcer: 'If history repeats itself, I should think we can expect the same thing again.'

6. Basketball analyst: 'He dribbles a lot and the opposition doesn't like it. In fact you can see it all over their faces.'

7. At the rowing medal ceremony: 'Ah, isn't that nice, the wife of the IOC president is hugging the cox of the British crew.'

8. Soccer commentator: 'Julian Dicks is everywhere. It's like they've got eleven Dicks on the field.'

9. Tennis commentator: 'One of the reasons Andy is playing so well is that, before the final round, his wife takes out his balls and kisses them... Oh my God, what have I just said?'

• ## Remove Numbers From Alphanumeric String

Re: Alphanumeric

With your alphanumeric in A1, use the following two formulas:

B1: =SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(A1,"1",""),"2",""), "3",""),"4",""),"5",""), "6",""),"7",""),"8","")

C1: =SUBSTITUTE( SUBSTITUTE(B1,"9",""),"0","")

• ## Remove Numbers From Alphanumeric String

Re: Alphanumeric

• ## Remove Apostrophes From Cells

Re: Replacing A Single Quote With Nothing

Very first google hit: Death to all apostrophes

Don't forget to use code tags like this, but without the spaces... [ code ] blah [ / code ]

• ## Ensure Workbook Has No Macros

Re: Save And Close File If It Contains A Specific Module Or User Form

Use a Workbook Open event where you turn a variable on so that you know the file you're opening is the one you want.

• ## Reverse Concatenation

Re: Reverse Concatenate?

much better :cheers:

• ## Reverse Concatenation

Re: Reverse Concatenate?

err, almost, C1 isn't right

• ## Reverse Concatenation

Re: Reverse Concatenate?

try...

B1: =RIGHT(LEFT(A1,FIND(",",A1,1)-1),50)
C1: =LEFT(RIGHT(A1,FIND(" ",A1,1)),3)
D1: =RIGHT(A1,5)

• ## Reverse Concatenation

Re: Reverse Concatenate?

highlight columnA and goto menu: Data\Text to Columns...

• ## Count Used Cells In Range

Re: Return Row Count

I don't know how Yard and Cheeky even understood what you wanted. If they didn't either; maybe a sample file would help...

• ## Move Worksheet From One Workbook To Another

Re: Move Worksheet From One Workbook To Another

You need to get the date from somewhere...

Is there a useable date in the .CSV file?
Would you be able to base it off of the day you are running the file? i.e. Do you run it on Monday's and need the previous week?
What date do you need it to be set to?

Code
``````Sub MoveWs()
Sheet2.Move Before:=Workbooks("Book2.xls").Sheets(1)
End Sub``````
• ## Highlight Formula Reference On Another Sheet

Re: Highlight Formula Reference On Another Sheet

Without knowing "why" you want the cells highlighted in the 2nd book, would this be useful to you?

select your cell containing the formula and goto menu: Tools\Formula Auditing\Trace Precedents, now blue lines will connect to your cells in the active workbook and any references to your other book will have dashed black line with a small sheet icon. If you double click the dashed line it will bring a popup box with all of the links and you can choose one and it will goto the 2nd book and highlight that cell.

• ## Select Rows Based On Column Fitting Criteria

Re: Selecting Multiple Entries Within A Large Database

I don't know "how" you want this to happen, but one way would be to just goto menu: Data\Filter\AutoFilter then just filter for your criteria and highlight all the rows, copy/paste

• ## Create Popup Message For Cell

Re: Create Popup Windows