 # trying to calculate an end value

• good day

i have written a macro to find the answer to a sum i have for a single line in userforms Excel vba
but now trying to do the same through a loop i am not getting the textbox values

below is the code i am using currently that picks up text box names but not the values in the textbox

Private Sub CalculateGroup()
Dim WC As String
Dim W1 As String
Dim W2 As String
Dim D As String
Dim R As String
Dim T As Double
Dim W As String
Dim Q As String
Dim i As Integer
Dim J As Integer
Dim test As Integer

For i = 1 To 4

'test = UserForm3.Controls("Txtbox" & i & "A" & J).Value <----- this is the textbox where the answer goes

WC = "Txtbox" & i & "A" & 2
W1 = "Txtbox" & i & "A" & 3
W2 = "Txtbox" & i & "A" & 4

R = (Val(W1) + Val(WC)) - Val(W2)
T = Val(R)

Q = Val(R) / Val(W1)
W = Val(Q) * 100
'test.Value = W
' test.Text = Format(test, "0.00")

Next i

End Sub

i have tried Val("Txtbox" & i & "A" & 2) but it gives a 0 instead of textbox value

if anyone could help it would be much appreciated

• Hello,

To speed up designing a customized solution ... you should attach a sample file ...

Most probably CLng() should do the job ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner • if you go to userform 3 page 3 there will be values in Txtbox for WC, W1 and W2
i am trying to get an answer for the moisture Content,

the formula i am testing and playing with is at the bottom of the code in userform 3, once i have it able to get all final values i can use same formula for my other calculations

[ATTACH]n1225851[/ATTACH]

## Files

• i have the formula to just get the first lines answer but want to be able to loop through lines so that my code wont be as long.

if you have any advice it would be much apreciated

sorry you may have to zoom the userform to 100 i am not sure if i set it back

• Extremely confusing ... :wink:

To be tested

Hope this will help

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner • it runs the same as i had it except it is giving me a type mismatch error once the CDbl() function is used thats wht the Val() function worked the best here,

all i am getting stuck on is trying to find the Numeric Value of the things so when i F8 through it should read as follows when i hover mice over the codes

WC = 22.55
W1 = 100.04
W2 = 122.63

WC = Txtbox1A2
W1 = Txtbox1A3
W2 = Txtbox1A4

and so on

• the way my naming works is the the box is labled
Txtbox1A2 meaning the first number is my row and second is the column number, column is seen as J
i = 1
J = 2

• Just a couple of questions :

1. What is the final objective ? The sum of all the TextBoxes ?

2. Are all these Values already stored in your sheet ... or not ?

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner • the final objective is to use the calculations to find a value for W which i have shown in my first post with the code, i have messed around a bit and have worked it out thank you for your help

• Thanks for the feedback

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner 