A While/Wend loop can only be exited prematurely with a GOTO or by exiting from an outer block (Exit sub/function or another exitable loop) Change to a Do loop instead: Do While True count = count + 1 If count = 10 Then Exit Do End If Loop Or for looping a set number of … Read more
UPDATE: After all the details summarized and discussed, I spent 2 fair hours in checking the options, and this update is to dot all is. Preparations First of all, I performed a clean Office 2010 x86 install on Clean Win7 SP1 Ultimate x64 virtual machine powered by VMWare (this is usual routine for my everyday … Read more
Dim a As Range, b As Range Set a = Selection For Each b In a.Rows MsgBox b.Address Next
=TEXT(A1,”DD/MM/YYYY hh:mm:ss”) (24 hour time) =TEXT(A1,”DD/MM/YYYY hh:mm:ss AM/PM”) (standard time)
Using following simple formula is much faster =LOOKUP(2,1/(A:A<>””),A:A) For Excel 2003: =LOOKUP(2,1/(A1:A65535<>””),A1:A65535) It gives you following advantages: it’s not array formula it’s not volatile formula Explanation: (A:A<>””) returns array {TRUE,TRUE,..,FALSE,..} 1/(A:A<>””) modifies this array to {1,1,..,#DIV/0!,..}. Since LOOKUP expects sorted array in ascending order, and taking into account that if the LOOKUP function can not … Read more
You can easily do this with the normal “Time” data type – just change the format! Excels time/date format is simply 1.0 equals 1 full day (starting on 1/1/1900). So 36 hours would be 1.5. If you change the format to [h]:mm, you’ll see 36:00. Therefore, if you want to work with durations, you can … Read more
Right click on Cell B1 and choose Format Cells. In Custom, put the following in the text box labeled Type: [h]:mm:ss.000 To set this in code, you can do something like: Range(“A1”).NumberFormat = “[h]:mm:ss.000” That should give you what you’re looking for. NOTE: Specially formatted fields often require that the column width be wide enough … Read more
Quite often the issue is a non-breaking space – CHAR(160) – especially from Web text sources -that CLEAN can’t remove, so I would go a step further than this and try a formula like this which replaces any non-breaking spaces with a standard one =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),” “))) Ron de Bruin has an excellent post on tips … Read more
For second accuracy, yyyy-MM-dd HH:mm:ss should do the trick. I believe Excel is not very good with fractions of a second (loses them when interacting with COM object IIRC).
You can add the image into a comment. Right-click cell > Insert Comment > right-click on shaded (grey area) on outside of comment box > Format Comment > Colors and Lines > Fill > Color > Fill Effects > Picture > (Browse to picture) > Click OK Image will appear on hover over. Microsoft Office … Read more