Increment Until?

[McLaren]

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.

 

[fairwinds]

Hi,

What formulas are in E4/E6/E8/E10/E12/E14 ?

 

[McLaren]

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.

 

[fairwinds]

What about putting a formula in E2 ?

We could also add some rounding to it to fit your standard heights.

Book1
	D	E	F	G	H	I	J
2		73.76913
3	Factors
4	1.1	81.14604
5
6	1.2	88.52296
7
8	1.3	95.89987
9
10	0.7	51.63839
11
12	0.8	59.0153
13
14	0.9	66.39222
15		100		2		2
16
17
18
19							0
Sheet1

 

[McLaren]

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.

 

[McLaren]

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>
E4= =ROUND(PRODUCT(D74,E2),3)

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.

 

[McLaren]

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?

 

[GS2000]

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

 

[McLaren]

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?

 

[McLaren]

The crisis has been overted, now I have a few other issues. Gonna close this thread out and start new. Thanks for the help.