Search results

Happy to share this with the community Sub ListLinks() Dim ws As Worksheet Dim arrForm As Variant Dim LastRow As Long Dim LastCol As Long Dim ctrRow As Long Dim ctrCol As Long Dim CellsWithForm...

Great response; perfect answer. Thank you so much.

I'm attempting to speed things up when searching a multi-sheet workbook, cell by cell, for links within a cell. My approach is to use INSTR to find the value ".xl" within the actual formula text. When I do this cell by cell, I have to use artifacts like cell.formula and cell.hasformula. I'd...

Your first suggestion was PERFECT! Thank you so much!

Marc, thank you for your response and solution. I'm using xlapp. because this is running from an ACCESS VBA module. The workbooks are "open" and accessible, but they don't open in the traditional Excel macro sense. Any idea why? I can put checkpoints in the macro and get workbook and worksheet...

I'm out of my depth here. I'm opening 5 workbooks dim'd as Objects (the code lives in an Access module, that's why they're objects). All but one of the workbooks have one sheet. One workbook has 4 sheets. I start the macro with an Excel.Application object and end the macro quitting the...

Fluff13, this is incredibly elegant. Even though I've considered myself quite conversant with VBA and Excel constructs, I'm having difficultly understanding exactly what Ary = Application.Index(Range("A1:AF" & Usdrws).Value2, Evaluate("row(1:" & Usdrws & ")"), Array(1, 8, 11, 15)) is doing.

Thank you, Hui. I've enjoyed Chip's material for quite some time. What I did was create N 1 column arrays + a "master array" with N columns, loaded each individually, then since they're all the same length, filled the master array with direct assignments to the rows in each of the individual...

Situation: 500,000 rows with 12 columns. I need 3 of the columns, and they're not contiguous. I want to load an array(500000,3), but I want to load each dimension separately - is such an ask possible?

What do you see if you press Ctl-Alt-V?

chirayu, I don't understand the modification you're suggesting to your code. Can you elaborate?

Thanks to both p45cal and chirayu. The results are in . . . Range is 732,840 rows by 7 columns My "old" way = 37 sec For ctr = FirstRow + 1 To LastRow If ctr Mod 2 <> 0 Then With Range(Cells(ctr, 1), Cells(ctr, LastCol)) .Interior.Color = RGB(220, 230, 241)...

I have a common formatting routine that for a given worksheet computes the last row, loops through each row and depending if ROWNUM MOD 2 is 0 or 1 formats the entire row with alternating colors, aka Zebra formatting. Never thought this process was that "expensive", performance-wise, until...

chirayu, you've been incredibly helpful. I'd like to make this agnostic to a specific macro. Is there a way to add one more combo box to create a list of all macros in open *.XLSM workbooks? End result, pick a workbook, pick a worksheet, pick a macro.

chirayu, thank you very much. Here's a really stupid question: How would I put this form on a sheet in the Sample.xlsm file? I went into Alt-F11 and ran the Macro TEST.

I have a number of stand-alone macros that I run against worksheets in other open workbooks. Let's say my macro workbook is Chandoo.xlsm, one of my many open workbooks is Target.xlsx, and there are many worksheets in Target.xlsx. I'm looking for a dialog process that will prompt me for which...

Does Excel have issues using INDIRECT with a named range defined by a dynamic formula? Example: A1 = Chandoo (and named ChandooNR) CI = ChandooNR =INDIRECT(ChandooNR) returns the value Chandoo. It's what we'd expect. But, when I create a dynamic named range called ChandooDNR defined by...

I really appreciate the assistance.

Language is US English Excel Version is 2013 32-bit

I Googled Scientific Notation https://en.wikipedia.org/wiki/Scientific_notation Perhaps this bullet from Examples and other notations explains the "acceptance" of D FORTRAN (at least since FORTRAN IV as of 1961) also uses "D" to signify double precision numbers in scientific notation.[7] If...

Chihiro, why would it be correct to interpret 613609D60 as numeric? I fully understand if the letter were E rather than D that Excel would believe the value is SciNot, but I've never seen any letter other than E resolve back into a numeric value.

Thank you both. Here's the VBE display answering the question as to quotes, or not ? format("613609D60","000000000") 613609000000000000000000000000000000000000000000000000000000000000 ? format(613609D60,"000000000") 613609000000000000000000000000000000000000000000000000000000000000 Quotes...

I'm having trouble with VBA converting what should be a text value into a Scientific Notation value. For Value = 613609A60, the formula Format(Value,"000000000") returns 613609A60, as we'd expect. For Value = 613609E60, the formula Format(Value,"000000000")...

a genuine thanks for the link, Hui. I came across this same post earlier and that's why bullet 3 in the original post was Authoring massive lines of complex code Perhaps some things are meant to be, but it just seems to make little sense that the VBA editor would provide a feedback loop...

Hui, I suspect I wasn't clear about my request (unless you're pulling my leg :)). I want to do it using VBA, for example calling a little subroutine at the top of the procedure to clean out whatever debug.print messages have been left behind from prior runs. Make sense?