Search results

Trimmed Sajan's formula down to 129 characters by removing the +1, changing the +0 to -, removing a pair of unnecessary brackets. =MID(A1,MODE(MMULT((N(ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))={1,0})*(ROW(INDIRECT("1:"&LEN(A1)))-{0,1}),{1;1}))+1,255) Of course...

When you do the text to columns (following the steps I outlined), you can set all of the columns to text in one go. When you select the 2nd column in the preview pane, hold down the shift key and then click on the last column. This will select all of the columns apart from the 1st one. Then set...

Here's a basic starting point: Sub recordreference() ' OpenExcel Macro Dim xlApp As Object Dim xlWkb As Object Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True Set xlWkb = xlApp.Workbooks.Open("C:YourDocumentsBook5.xlsx") 'change path as necessary...

Hi, Data > Text To Columns Delimited > Next Other Delimiter | > Next In the data preview pane, left click on the second column and then at the top choose Text format for that column. > Finish

If you sort your data by column W then you can make the formula much simpler and faster: In row 3 (assuming row 3 is the first row of data) =IF(W3=W2,"Duplicate","") and then fill down.

I like the idea and I like the sticky. One more question which perhaps the sticky could address: once you're a ninja are you always a ninja, or do you have to remain active to keep the title?

Hi, The criteria needs to be ">"&B2 instead of ">B2". The criteria you're using at the moment is a comparison against the word "B2" rather than against the value in the cell B2.

The information you've received from the Skype development platform is misleading: they are confusing VBA and (presumably) VB.Net. "You have to compile your code for 32-bit platform" is for VB.Net (or C# etc). It sounds like Skype4COM.dll is a 32-bit COM dll which means that you cannot load it...

Congratulations!!

Very good, sir. :)

Just throwing the thought out there... a nice feature to add to the forum would be a member list page similar to the one we see with VBulletin software powered forums.

Congratulations on all of your great posts - keep it up! :)

I also made a few quick changes to get rid of those very slow array formulas. Using a pivot table speeds things up a lot. I deleted all of the array formulas. In E1 I put the word Day and then in E2 I put the formula =LEFT(TEXT(B2,"dd"),2)+0 In F1 I put the word Month and then in F2...

Hi, the reason SMALL() displays 0 in case of positive values is because your IF() statement returns 0s if the condition is not met. Here's your formula again: {=SMALL(IF((LEFT(TEXT($B$2:$B$65384;"dd/mm/yyyy");2)...

Here's a quick and dirty way - Sub foo() If Sheet1.[=AND(A1:A10=B1:B10)] Then RunSomeCode End If End Sub Sub RunSomeCode() MsgBox "ranges are the same" End Sub

A good way would be to use a pivot table. If you don't have one, add a column header in the first row. Let's suppose you use the header "Dates". Create a pivot table which refers to your data (including the header row). Add "Dates" to both the row labels and the Values sections of the pivot...

Hi Narayan, The windows timer workaround on that link is one of the two 'theoretical' options that I referred to in my previous post. I had a feeling someone might bring one of them up so I covered myself! :) It can make Excel extremely unstable (crash Excel) unless handled very delicately...

Sorry, no - if the originating call is made from a range then it all gets locked down. Calling a sub from your function won't help. Using a simple formula of =STDEV(data)/AVERAGE(data) and then tuning some conditional formatting to highlight the cell as necessary will be more efficient in...

Welcome to the forum and rest easy, you're not making a stupid mistake. :) When a VBA function is called from a range, a golden rule is that the function is not allowed to change the Excel environment: it can only return a value (or set of values) back to the range. Changing a cell's colour...

The best way to find out is to give it a try! You should remove the Range.Select and Selection object from your code though because it'll error if you try to select a range which isn't on the active sheet... ActiveWorkbook.Worksheets("Data").Range("E8").Select...

Hi, Possible, yes, but it wouldn't be easy. Why don't you open each workbook using your code - that way you wouldn't have to open them manually? The code would look something like this: Sub foo() 'list all of the 7 file names here, separated by commas Const strFILE_NAMES As String =...

Hi, The 7 open files must be open in the same Excel instance as the workbook which contains the code. If they're open in a different instance of Excel then the code won't see them.

Setting Application.EnableEvents = False should prevent the Worksheet_Change() event handler from being called. Please post the relevant code (including the worksheet event handlers) so we can investigate it further?

I would definitely stick with converting the book into an xlsm and trying it from there. In my experiece running workbooks in compatability mode can give all sorts of bugs which were fixed once the workbooks had been compatability checked and converted. The pivot table model changed quite a bit...

Ah, that would explain it then. Since your OS is 64-bit but the msstkprp.dll is a 32-bit component, you need to copy and register it in C:WindowsSysWOW64 instead of C:WindowsSystem. NB. Use the C:WindowsSysWOW64regsvr32.exe to register it. Google for how to register 32-bit dll on 64-bit...