Increment Until?


Board Regular
Jul 12, 2003
I have been searching the boards with no luck as of yet, hopefully someone can point me in the right direction.
I am trying to get the largest height characters that will still fit within a given width (for engraving on a CNC machine). So what I need to happen is the user puts in a height, and that height will shrink itself until the math works out so it fits the width. What I have so far is:

E2 = character height
E4/E6/E8/E10/E12/E14 = each of 6 engraving strings width based on the E2 height
E15 = maximum width available
G15/I15 = unusable width
J19 = "=IF(E15-G15-I15<=MAX(E4,E6,E8,E10,E12,E14)+0.1,0.0001,0)"

So if the largest string(plus 1/10 of an inch) is too wide to fit the usable width than I'd like it to subtract .0001 from E2 and try it all again.

I know this is creating a circular reference, but I'm not sure how to get around it.
Thank you in advance for any and all replies.

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Unfortunately the file is back at work, and I am home for the night. But it is the product of E2 and another cells value.
What happens is the user inputs a text string into a cell. Another group of cells figures out how many of each character (A-Z,0-9,etc) are in that string and multiplies the number of each character by a predefined factor. As an example the factor for:
A = 1.0506
B = 0.9455
C = 0.9471
\ = 0.9559

So the string "A\BBA" would put the value 4.9481 into cell D72. If I wanted .500" high characters this string would be 2.47405 inches long. So it takes D72's value and multiplies it by E2. The part I want to engrave on is 1.38" long, so 1/2 inch high characters is too much.
Upvote 0
Because E2 is kinda the starting point. I only want to mess with the users inputed height if and only if it would be too wide of a string to fit.
Upvote 0
I almost have it.

I19= =IF(E15-G15-I15<=MAX(E4,E6,E8,E10,E12,E14)+0.1,0.0001,0)
I20= =IF(I19=0.0001,E2-I19,E2)

Private Sub TextBox1_Change()
Range("E2").Value = TextBox1.Value
TextBox3.Value = Range("E4").Value
TextBox14.Value = Range("I4").Value
End Sub

Private Sub TextBox2_Change()
TextBox2.Text = UCase(TextBox2.Text)
Range("E2").Value = TextBox1.Value
Range("E3").Value = TextBox2.Value
TextBox3.Value = Range("E4").Value
TextBox14.Value = Range("I4").Value
TextBox1.Value = Range("I20").Value
End Sub

So if the string is too wide I20 becomes .0001 less than E2 and TextBox2 sets TextBox1 with I20's value, and TextBox1 sets E2 with TextBox1's value, which would be .0001 less than original.

Now what am I doing wrong that prevents the spreadsheet from updating with the new E2 value? Such as>

It's not using the new E2 value to recalculate the string width, thereby changing I19 to maybe being good.

As always any and all replies are appreciated.
Upvote 0
Hrm, if I change TextBox1 myself it updates everything automagically. How do I have the sheet update whenever TextBox1 changes based on another textbox changing?
Upvote 0
Could you not have the calculation based on the sheet changing instead and use the Worksheet_Change(ByVal Target As Range)

I've only had a quick scan at the problem so this may be well off target

restrict its fuction with ifs.

Not a solution but a quick cut and of some code, not complete!!! below gives an idea.

Private Sub Worksheet_Change(ByVal Target As Range)
ThisColumn = Target.Column
ThisRow = Target.Row
' change the col and rows below  to where you want it to work
' currently set to react if cell 1,1 changes, you will also need another if stament to stop the cycle when
'a satisfactory value is reached
If ThisColumn = 1 And ThisRow = 1 Then ' amend this as you ned it to the relevant cell
    'put our code here
     End If

End Sub
Upvote 0
I'll have to look into it. Right off the bat I don't understand it, but I will try to figure out what you have going on. I assume it goes in my VBA script?
Upvote 0
The crisis has been overted, now I have a few other issues. Gonna close this thread out and start new. Thanks for the help.
Upvote 0

Forum statistics

Latest member
Rich Cohen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back