Search results

Some of the text strings have an embedded LF (char(10)) embedded. That's appears to where it's breaking. No instances of blanks between the , and the = that I can see. Thanks for discussing this with me.

I like where you're going with the inference. It gets more bizarre - the vast majority of fields have the =T construct, but many do not. That's where Excel chokes and begins guessing when to ignore the =T values and when not to - it fails occasionally.

Luke, it's not. Thanks for the followup.

A co-worker recently received a .csv file where each field was delimited by =T("Field Value") So you ended up with =T("Field Value"), =T("Field Value"),=T("Field Value"), etc. Excel appears to recognize this and parses it appropriately. Can anyone shed any light on this syntax?

Luke/Narayan, thanks for the replies. - Narayan, what does the + 0 do after both MID expressions? - Luke, you hit the nail on the head - the spacing is inconsistent (you'd expect no space for a leading 0, but instead it's there giving Narayan's formula a foothold for position location)

Hello there. I try only to post after I've exhausted every possible thing I can think of. There's a date parsing issue i just posted, and, if you have time, I'd appreciate your take on it. Thank you, and I hope all is well with you.

I just cannot seem to figure this one out. I've attached a file with a column of what appear to be dates looking like Jun 30 1989 12:00AM (mmm d yyyy HH:MMAM/PM). Try as I might with Text to Columns, changing the cell's format from general to date, adding 0 to the cell, DATEVALUE(), etc. I...

INDIRECT (why didn't I think of that) should fit the bill quite nicely. Once again, Narayan, I'm in your debt. Thank you.

I have a named range in my Macro workbook that is used by a separate workbook. When I distribute both the Macro workbook and the other workbook to others to use, the vlookup reference in the other workbook "breaks" as it appears to be using the full path name back to the macro workbook rather...

Narayan, I cannot thank you enough. You've helped me out a couple of times recently, and awhile back "liked" a piece of code I submitted to optimize another problem. It's wordy, but ChandooNarayan.org might be on the horizon.;)

Narayan, I guess I'm "happy" that #3 is the case, buy can you please explain why this would be the case? I certainly understand the case where the location of the LU file could be intuited from the location of the .xlsm file. Does Excel make the location of each open Excel file available to VBA...

The macro works when the LU .xlsx workbook is open, and is in the same directory as the macro, and I DON'T qualify the lookup workbook with a path The macro fails when the LU .xlsx workbook is open, and IS NOT in the same directory as the macro, and I DO qualify (I have to, right?) the...

Narayan, Well, don't I feel stupid! What happened to the other 1,000 unused rows? If I were up against memory constraints, wouldn't I have an issue with Excel allocating space it would never use (theoretical question, really)?

Narayan, if I dump the non-REDIMed array, I end up with #N/A values in all the cells I didn't fill during the loop. It seems like it should be a common issue: not knowing in advance how many rows one will be writing, having to over allocate in advance, then not wanting to end up with the #N/A...

Thank you for taking the time to look at this. Here's a modification to the macro. Since I don't know how many rows I'll be writing out, I'm forced (I think) to over allocate at the beginning then REDIM right before I dump the array. It is true that you cannot REDIM the first dimension? Thinking...

I have about 8,000 rows with 94 columns. I read the entire range into an array, then process the array 1 by 1 writing out to another array until I hit the end. Based on logic applied to one of the fields in the array, I may end up replicating one row in the first array multiple time into the...

Thanks, Luke. I developed this, which is lightening fast (probably an accident). Option Explicit Sub LastRowInNColumns() Dim LastRow As Long 'LastRow in Column being searched Dim AddRemRow As Long 'next row position to add range from Column being searched Dim CurrCol As Long 'Column being...

I've attached a file with 4 columns and 5 rows (excluding the header row). I want to create a new column containing only the unique values for the 25 values (in my example it's 8). I'd like the process to run for N columns. Any ideas would be appreciated.

I'm doing a straightforward two file match and I have an instance where I expect to advance the key in File A because it's less than the key in File B. Doing a comparison in Excel, it's less than, but the VBA compare says is greater than. Here are the two values: 1799tmp_0_13846IIE...

Thanks to all for responding. After pounding my head against the wall, here's what I discovered: What I presumed to be the problem, actually wasn't. I was attempting to Transpose roughly 2,400 rows and 5 columns. Easy, right? Well, the native "move" of the array into the spreadsheet worked...

but it works, if I change to "as String". I have no idea why. The array is 5 x 2834. The first vector is always a number, the remaining 4 are often filled with text, sometimes blanks (not nulls, blanks.) Any ideas would be very much appreciated.

Thank you for all of your help.

Luke, your solution works but, for 7,000 rows, is very slow. I stumbled across this. It's virtually instantanous. I'm embarrassed to say I don't really know how/why it works especially with regards to the WITH construct. Thank you for your prior responses. Can you shed some some light on why...

Luke, the data's not filtered. I'm going traversing the data one row at a time, testing the value in column C, and depending on the result issuing a EntireRow.Delete command. Do While Range("A" & ctrRow) <> 0 If Range("C" & ctrRow) < Range("CurrentDateMinus10") Then...

I'm trying to delete several thousand entire rows, and it's taking way too long. I've turned off screenupdating and auto calculation with noticing much improvement. Any ideas?