Inserting textbox value into a formula

[pmgibs]

Hi,

I have a very large validated worksheet that may require updating in the future. I am trying to create a Userform that staff will enter new values in a textbox and these new values will insert themselves into the formula. This way I can just validate the Userform functionality rather than validating to entire worksheet.

This is my current code:

Dim cell As Range    For Each cell In Range("E2")
        cell.Formula = Replace(cell.Formula, "IF(AND($B2>=270,$B2<=325,$D2>=330000000000,$D2<510000000000,$C2>=900,$C2<2001),""SV"",", "=IF(AND($B2>=" + TextBox47.Value + ",$B2<=" + TextBox48.Value + ",$D2>=" + TextBox43.Value * 100000000000# + ",$D2<" + TextBox44.Value * 100000000000# + ",$C2>=" + TextBox45.Value + ",$C2<" + TextBox46.Value + "),""SV"",")
        cell.Formula = Replace(cell.Formula, "IF(AND($B2>=326,$B2<391,$D2>=330000000000,$D2<530000000000,$C2>=800,$C2<1701),""LV"",", "IF(AND($B2>=" + TextBox41.Value + ",$B2<" + TextBox42.Value + ",$D2>=" + TextBox37.Value * 100000000000# + ",$D2<" + TextBox38.Value * 100000000000# + ",$C2>=" + TextBox39.Value + ",$C2<" + TextBox40.Value + "),""LV"",")
        cell.Formula = Replace(cell.Formula, "IF(AND($B2>=300,$B2<390,$D2>=330000000000,$D2<530000000000,$C2>=800,$C2<1701),""DS(1)"",", "IF(AND($B2>=" + TextBox35.Value + ",$B2<" + TextBox36.Value + ",$D2>=" + TextBox31.Value * 100000000000# + ",$D2<" + TextBox32.Value * 100000000000# + ",$C2>=" + TextBox33.Value + ",$C2<" + TextBox34.Value + "),""DS(1)"",")
        cell.Formula = Replace(cell.Formula, "IF(AND($B2>=375,$B2<421,$D2>=680000000000,$D2<800000000000,$C2>=1300,$C2<2101),""DS(2)"",""""))))", "IF(AND($B2>=" + TextBox29.Value + ",$B2<" + TextBox30.Value + ",$D2>=" + TextBox25.Value * 100000000000# + ",$D2<" + TextBox26.Value * 100000000000# + ",$C2>=" + TextBox37.Value + ",$C2<" + TextBox28.Value + "),""DS(2)"",""""))))")
    Next cell

To further explain in the first line of 'cell.Formula = ...' line I would like Textbox 47 value inserted into the formula inplace of "270". I encounter a Run-time error '13': Type mismatch at this point.

Any help would be greatly appreciated.

 

[Paul Ked]

Textboxes, as the name implies, are formatted text. Try CLng(TextBox47) or CDbl(TextBox47) depending on the number format you want.