Posts by maxihoya

Re: Running Useform Textbox Calculation

Maybe it is more useful to have a command button to trigger.

Private Sub CommandButton1_Click()
     TextBox1.Value = Evaluate("=" & TextBox1.Value)
End Sub

Read more here

Re: Identify Bottom Left Cell in Named Range

Use Rich's if you want bottom left and Dave's if you want bottom right.

Re: compare values in columns?

=SUMPRODUCT((A2:A11=1)*(B2:B11=1)*(C2:C11=1))

Will count the number of rows in the first 10 rows where it is 1 in column A and B and C.

Re: Creating a graph with a macro

A good place to start is to record a macro of your charting process. You can then adapt the code to suit your needs.

Hi, first time question post and bit long to describe so please bear with me.

Aim
How do I optimise my computer's setting for running strenuous loops?

Context
I've built a model in Excel. The model is built in vba code, i.e. the code reads all the relevant information from the Excel sheet into memory. The code then outputs results based on the inputs. All the calculation is done in the memory. I've built an algorithm to run thru the combinations of different inputs. The point of this is to run thru all possible combinations (in the hundred thousands) of inputs to find an optimum result. I've minimised reading and writing onto worksheets as this takes up time, i.e. it reads the input only once in the beginning and calculates everything on the fly and writes each result to worksheets. This method makes it a lot faster than writing formulas in worksheets and changing inputs on worksheets.

I've tested the model on a few computers, and some less powerful (including less RAM) computers run the model faster (almost twice as fast). Why is that?

Question
How do I optimise the system settings so that this type of application (running loops in memory) runs the fastest?

Thanks for reading. Look forward to hearing your ideas.

Re: splitting cell data into columns / data cleaning

It seems like the delimiter is " " (2 spaces). Find/replace the 2 spaces with something unique to your entries and use that as the delimiter. You'll get something close to what you're after.

Re: Formula for weekly update

=NOW() gives you the date/time now

Re: A conundrum of variables

Could you post an example workbook to illustrate your question?

Re: Remove the first digit from a numeric and replace with remainder

Comparing apples to apples...

=value(RIGHT(B2,5))

Re: Remove the first digit from a numeric and replace with remainder

As wattch mentioned - this gets rid of the formula and leaves only the values of the stock codes.

Otherwise, when you search (Ctrl-F) you should "Look in Values".

Re: Remove the first digit from a numeric and replace with remainder

=right(A1,5)

Re: replacing n/a

=IF(ISERROR(LOOKUP(M9,Sheet2!D3:F11)*(M9-Sheet2!E11)),"",LOOKUP(M9,Sheet2!D3:F11)*(M9-Sheet2!E11))

Re: Hide / Unhide columns

Try recording a macro and learning the language from the recording output.

Re: Trying to sum duplicate values

Am I missing something here? Pivot tables will do exactly what you're after (assuming I'm understanding what you want to do correctly)

See attached workbook.

A problem is only as hard as you want to make it...

Re: Trying to sum duplicate values

Why not use pivot tables?

Re: maximum match

An easy way would be to create a dummy column. See attachment

Re: moving from A to B in VBA?

This should get you started - A-Z is Chr(65) to Chr(90)
Use

x = x + 1

to increment

Re: Name Box Shortcut Key

Ctrl-F3

Re: Let User Know Then Move On

I suggest that you use the status bar, example code:

Application.StatusBar = "Processing - " & strCurrentName
Application.StatusBar = False 'Remember to reset the status bar

To delay use something like:

Application.Wait Now + TimeSerial(0, 0, 5)

Re: Excel Macro / Formula

You can do this in many ways...

One solution is to use conditional formatting. Options>Conditional Formatting

Formula Is: =$A$1="Yes"