Search results

Ctl-Shift-1 is exactly what the doctor order. Thank you GraH-Guido.

The Comma option puts the number in Accounting format, so it doesn't do what I need it to do. Thank you for the suggestion.

My preferred number formatting is #,##0 Is there a way to automate this by "replacing" what Excel says is Number (I have to go in and opt in for Use 1000 separator and set the Decimal places to 0)? I have another which is #,##0;-#,##0;;@ Again, I'd like to do this with one button simplicity...

You, my friend, are a rock star! Well deserved Ninja designation. The =MATCH("zzz",C:C) formula works exactly the way I requested, and it's elegant. Thank you.

Thank you, bosco_yip. See the attached, please. My requirement is that the formula returning the last row be in the column in which the derivation is being done. Your formula is returning a circular reference. See column 1 - that's the overly complex one mentioned in my initial post. Does it...

Peter, thank you for the response. Any suggestions for those not on 365?

Easy to do in VBA, not so much in Excel. Often, I put totals in Row 1 followed by a column header, and the data beginning in row 3. I'm constantly having to do something like =countif(A3:A10000,"ValueToTest") knowing in this instance there would never be anything near 10,000 rows. Seems like...

That's the answer I need. Many thanks.

Marc L., this is probably a stupid question, but is there a way to "connect" to an workbook without "opening" it, per se, in order to read it only - no actions against it.

Since this posted on the VBA forum, my issue relates to the file being opened within the macro.

Saw a comment referring to ADO. Haven't really delved into it, but it seems like it avoid the physical overhead of "opening" the file. When you say "Just remove any useless thing," to what specifically referring?

You understand that this is a macro, right? The user is prompted to browse for the file.

It's taking almost 9sec to open a measly 8mb workbook. I only need to interrogate the data in the WB's 2 tabs. I write nothing to the WB, I don't sort. Nothing. Any suggestions would be welcome.

This was perfect! Thank you so much. I'm using Call Shell(Environ("COMSPEC") & " /c copy """ & Src_Pattern & """ """ & Target & """", vbMinimizedFocus) One last question: I need to add the parameter /b immediately following Src_Pattern. How would I place it? The triple quotes are (and I'm...

This is driving me nuts. I'm merging all files with the mask *.txt into one .txt file using a DOS shell command. The command is Shell Environ$("COMSPEC") & " /c Copy C:\TEST\*.txt C:\TEST\CombinedFile.txt " I'd like to use variables for both the From and To environments. Because there's a...

What an impressive, straightforward solution. One change, the semicolon needs to be a comma, like so =MIN(IF($B$2:$B$6="",$A$2:$A$6))=$A2 Thank you, so much!

Range A2:A6 contains an integer value Range B2:B6 contains a blank or N value The objective is to highlight the cell in Range A2:A6 having the minimum value, but ONLY using those cells in Col A where its corresponding Test value is blank. So, in this example, the result of the formula s/b 2...

I need to know how to implement the SPLIT function to load the second array.

Here's what I want to do 1. Import a large (220MB) pipe-delimited txt file very quickly (this works). Let's say the file has 280,000 rows. At this point I'll have a 1 dimensional array (strData) with 280,000 rows. 2. Now, I want to split each row using the "|" character into another array...

I appreciate all these responses. My specific situation is that I have 4 dates each contained in a variable name dimmed as DATE. I suppose I could invoke a temporary step by writing them into a contiguous vertical or horizontal range, and do the COUNTIF technique. I was hoping :awesome: that...

Obvious answer uses the Transitive property we learned early on; you know, if A = B, and B= C then A = C. Could be written as =AND(Cell01=Cell02, Cell02=Cell03) = True For N values, though, is there an elegant using a CSE formula, perhaps? or something else? Thank you, in advance.

I should be able to do this, but I'm having difficulty. I have a range of values (A1:A5). Based on whether the value is Positive or Negative, I want to MULTIPLY the values together. Example 1 2 -3 4 -5 The formula (certainly an array/CSE type, I would think) would arrive at 8 (1* 2 * 4) for...

Thanks for both replies. Here's what I ended up doing. Started the module in Access Grabbed the Public variable denoting File Path Grabbed the File Date from the existing Input Box prompt Started an Excel instance Opened my Macro Workbook My run page had been pulling in Path and Dates from a...

I hope this question is "in bounds" for this forum. I've taken an awkwardly written VBA-driven process in Access and re-written it in Excel. How awkward you ask? What took 35 min now takes 8 seconds. The "old" Access process sets things like Run Date, File Paths, etc via an input box or by...

Not rocket science, but I've posted my macro. Useful for adding to one's Personal workbook, or utility collection. You can either uncomment the Debug.Print line, or write out to another tab. Thanks again.