Search results

Bob Phillips wrote a good article on Sumproduct here: http://www.xldynamic.com/source/xld.SUMPRODUCT.html This article, which I wrote back in 2008 (! time passes so quickly !), on array formulas may also be of interest: http://www.xtremevbtalk.com/showthread.php?t=296012 In post 5 I talk about...

Hi Narayan, Congratulations on your 6K and thank you for sharing your knowledge and experience with us. :)

Congratulations, SirJB7, on conquering yet another milestone! :cool:

Yep, it's a sneaky one, but they are different. The difference is the 'space' character between the X and the 4. In A3 it is a non-breaking space character. A simple way of checking this: I used the MID() function to split out all of the characters in each cell and then I used the CODE()...

If I copy PO BOX 123456 from your post into S2 and S3 in Excel and then compare them using =S2=S3, Excel returns TRUE. There must be a difference between the two values in your worksheet. The difference could be data type, eg. a numeric 123456 versus a string 123456 which you can check with the...

The first thing you should do is look in the Name manager to ensure that the names StartDateEntry and EndDateEntry both exist and that they correctly refer to ranges in the workbook. (1) If they do exist and you are getting the error, amend your VBA code and qualify both Range properties with...

Hi Harry, You can't change the signature of the combo_change() event handler by adding your own parameters. Furthermore, Static variables can only have procedural level scope. So, the direct answer to your question: is no, you cannot. One workaround is to use a module scope variable instead...

I tend to use a UDF to do this. Nice, Lori, I haven't seen (2) before. That could be handy! :)

Stick with it, I learnt a heck of a lot from that book and still regularly refer to the code examples in it. :)

Nice one, Luke. Keep those quality posts coming! :)

@sachinbizboy How are you getting on with VBA Developer's Handbook, 2nd Edition?

Congratulations SirJB on yet another milestone! :)

Congratulations, Hui!

The new forum software is a huge improvement: congrats and well done to everyone involved on the migration.

Another option... If you sort your pipelist so the numbers are descending: Std. Pie dia 76.2 63.5 50.8 etc... You can then use this simple formula: =INDEX(PipeList,MATCH(D2,PipeList,-1)) where D2 contains 12.1 The formula will return #N/A if D2 contains a value greater than the first...

Hi Shrivallabha, You're on the money again. :) In my work, I always use Value2 rather than Value, unless I am concerned about date/currency. Maybe there are some other edge cases when I would prefer Value but I can't think of any off the top of my head. As noted, Value2 is faster than Value...

That looks pretty good. :) One thing I picked up on is you need to strip the last character off the unicode string before you split it. Here's my first effort which has a few other small tweaks: Sub Test2() Dim strCharacters() As String strCharacters = CharArr(Range("A2").Value2)...

Hi Jeff, I think there are 2 key points to TRANSPOSE(): (1) As Shrivallabha pointed out, it is a context setter for the MID() function. (2) 1D arrays in VBA are horizontal. TRANSPOSE() converts the vertical row array to a horizontal one, so the output of the evaluation is a 1D array...

Hi Jeff, Yes, for example you can wrap it in TRANSPOSE() var = [TRANSPOSE(MID(B16,ROW(OFFSET(A1,,,LEN(B16))),1))] If you're using VBA anyway, then writing a function using native VBA methods will be more flexible, more robust and probably faster than this.

Hi, No, you can't declare a bunch of variables like that. The good news is there's a better alternative for you: use an array. If you give a bit more info then we might be able to suggest a bit more on the array (type, scope, dynamic or static, dimensions, etc), but essentially it sounds like...

Hi, I guess the question on my mind is why do you need to to put each row in its own array (or an array of arrays)? It would seem much more logical to use a 2 dimensional array of 3 columns and 600 rows. If you explain a bit more about where you need to go with this, we might be able to suggest...

It's because you have mixed data types in that column. The first 3 are strings but the last 2 are numbers. On my computer, which has XL 2007, I used: connString = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};IMEX=1;" & "DBQ=" & inputPath & ";" And I...

Hi, Here are two commonly suggested formulas: This one assumes there are two / and you want everything after the second one. It also assumes that "|@" will not occur in your data: =RIGHT(A1,LEN(A1)-SEARCH("|@",SUBSTITUTE(A1,"/","|@",2))) This one assumes that the 2nd...

Hi, PROPER() will change the first letter on each word to upper case and change all other letters to lower case. TRIM() will remove all spaces from a text string except for single spaces between words.

Hi, I didn't know about this limitation either. I haven't tested this but, if you have permissions on the SQL Server database, you could create a View called vYourView (give it a more appropriate name) along the lines of: SELECT TransactionFile.*, UserDeptFile.UserCode FROM TransactionFile...