Search results

Has anyone seen a clever way to do this without Using SendKeys (whose unpredictable behavior is legend) Establishing references to libraries Authoring massive lines of complex code Still shaking my head that such a straightforward process is so under served. Thank you in advance.

Mike, what I want is a range N cells high beginning N-1 above where my formula is.

I want to capture a range N cells tall PRIOR to my selected cell. I would have thought I could do something like OFFSET(AnchorCell,0,0,-N,1) and that would reverse the direction of the range. Doesn't work. Any ideas? Thanks in advance.

Exactly what I needed! Thank you PCosta87

Similar to SUMPRODUCT which adds, is there a way to multiply values based on criteria? For example, for every value of D in column BenchUpDown (Column F), I'd like to add 1 and multiply the values in Port(Column C). See the attached file. Ideally, I'd like to avoid the helper column in the...

Chihiro, you beat me me to it! Thank you for your response. The KB reference is https://support.microsoft.com/en-us/kb/213643

The following seven date values are contained in Column A rows 1 through 7 7/25/2016 7/26/2016 7/27/2016 7/28/2016 7/29/2016 7/30/2016 7/31/2016 I'm getting stuck using MATCH within VBA using Date lookups. Keeps coming back with a 1004 - not found message. Change the values to animal names and...

Thank you, Ramesh. I ended up doing exactly that, but I was curious as to why a "tried and true" formula was breaking. 3/31/2016 - 4/30/2016 - 1 month 5/1/2016 - 6/30/2016 - 1 month - Not sure why you're saying this is 1 month - May is a month, as is June. In my book, that's 2.

=datedif("3/31/2016","6/30/2016","m") Any idea why?

Sometimes two macros in the same workbook and module will display differently: one will show just the Sub Name, the other will show the FileName + Module Name + Sub Name. Any ideas?

This macro deletes all existing CFs, then sets 2 new ones: The first, based on a MOD 2 test vs. Row #, sets zebra stripes (light blue) The second, based on the cell >3 in columns 1 and 4, colors the cell/Font. It appears that the second condition isn't affecting cells (A4, A6, and D2) that...

I suspect it has something to do with LF vs. CRLF recognition by VBA, but I'm hoping someone can help me understand how to read this file (attached) record by record. Right now, EOF is reached after the first Line Input command and the contents of strline = to the entire file's contents. Excel...

I'm having a problem getting the RemoveDuplicates Method to work when i use Option Base 1. One way to employ the the RemoveDuplicates Method is to "feed" the Columns: option with (arrayname) where you've already loaded the array with a simple counter-based loop. It appears from my testing...

My macro is in its own dedicated workbook (.xlsm) (the name of the target workbook changes from period to period and the workbook name is effectively unknown.) I can always do a GetOpenFileName, but I'd like to do something other than that. I know the target workbook will be open I'd like to...

Narayan, you're right, formatting, pardon the pun, only masks the issue. I was trying to avoid a calculated field, but I may have to resort to it. Kanti, thank you for you response.

I have a simple pivot summing on one field. The source numbers sum to 0 for each field subtotal, but the Pivot Table subtotal is a very small number on the order of .0000000000-4. I've provided a sample showing the behavior. My actual file has several hundred unique fields with about 1/2 of...

Keyur, this will display the worksheet name and cell address (in the Immediate Window) wherever the search value exists in the workbook. Polarisking Sub test() Dim ws As Worksheet Dim c As Range For Each ws In ActiveWorkbook.Worksheets For Each c In...

A very nice, elegant solution. Thank you. The column-by-column move is definitely a big performance boost from cell-by-cell. I'm surmising that there's probably not a way to load a array dimension-by-dimension, then move the entire array over to the target area. Example: In this case, one...

Sorry, folks, for my lack of clarity. Here's the example in the attached file. My objective is to move very large quantities of data from Tab1 to Tab2 based on a table of eligible columns. My thinking was that the the most efficient way would be to read the eligible (highlighted) columns into a...

I have an array, pre-defined, defined as 1 to 900, 1 to 20. Those 20 represent non-contiguous columns. I'd like to load the array in chunks by column (or groups of columns) rather than doing the standar r = 1 to x, c= 1 to y. If the columns were contiguous, I could simply load the entire array...

Once again, you've delivered the solution. Thank you so much. Note: I fully understand the 0 offset, but for ease of understanding for those coming after me who might have to debug or amend the macro, I always insert Option Base 1 so the array begins at 1.

I'm trying to load a 6 element array with 6 defined string constants The "arr =" statement is failing on "Syntax Error". Any ideas? Dim arr As Variant Const varA As String = "A" Const varB As String = "B" Const varC As String = "C" Const varD As String = "D" Const varE As String =...

The Match("ZZZ",A:A) was what I was looking for . . .but, I have a question If the last value in Column A is numeric, the MATCH formula returns #N/A. I also tried CHAR(255) to insure I was testing for the highest possible value (ÿ). Any idea as to why it's failing? Logically, if you change the...

Mike & Luke, thanks for the response. I guess I'm trying to be too clever and arrive at the precise end of the data column in some misguided attempt to save several nanaseconds of performance overhead. Which brings up an interesting (I think) question: Rather than stopping at 10,000, why...

I want to create a SUM formula whose range is fungible. In rows 1 through 49, there's random, unpredictable cells of data. Row 50 is empty. In row 51, I have column titles; in rows 52 though N, I have data. I'd like to put a SUM formula above each Column title in row 50 - something like...