Convet string to double after replace function

[travelkid]

Please Login or Register  to view this content.

Tried to convert a string to a number after replace. Why isnt't that working? The type Name of T_01.Value is still string and not double...

[romperstomper]

The contents of a textbox are always text. You have to use variables, or use the CDbl when passing the arguments to the Average function.

[travelkid]

Okey, this code now takes the average of my values. But it should ignore empty cells, which it does not... I thought that WorksheetFunction.Average ignores empty cells. From my point of view there is no chance to use WorksheetFunction.AverageIf, because I cannot specify my values into a range...right?

Please Login or Register  to view this content.

[CK76]

WorksheetFunction does ignore blank "Cells" and does not behave in same manner for zero, which I suspect one of your variable holds.

I'd recommend doing Debug.Print of variables to check what value is held before the calculation (or use Locals Window/Watch Window to track).

How you should handle it, will depend on your desired result in specific use case.

I'd recommend uploading sample workbook which clearly demonstrate your issue with desired result for specific cases, if you need further help.

[travelkid]

Start userform "Aktualisieren_Heizung"
Click in the list box, take number 13
After the pick press "Wärmebedarf" --> the next userform will pop up.
--> the values from the database are printed out into the text boxes..
The 12 text boxes at the top show yearly values, the three values below show averages.
The first lower text box shows the average of "Heizwärmnebedarf" of 2015, 2016,2017, 2018. --> Thats where Im working on right now.. you can baically ignore the other boxes.

The change events are in there because changes of the text boxes T_01 ... T_04 wil have an effect on the average.

In the code you can ignore the the code, where a new entry is created... (new entries work)

The text box on the very top left shows the row number your working on..

Maybe this helps... I uncommented the change average events because error occurs.

UserForm6 you can also ignore, its just there to make the code work...

Best, Matthias



Let me know if you cant navigate and find my problem, I will make another sheet then. The code where my problem lies is in "Heizung1" around the end.. I have already made a suggestion how to solve it, but don't know how to write it out to the end properly...:

Something like that:

Please Login or Register  to view this content.

[CK76]

Not sure exactly where your issue is... but check LEN(Textbox.Value) and if it's = 0 then variable = null

Checking LEN() of string is faster than checking if it is = to vbNullstring. Though in this instance, speed won't matter much.

Ex:

Please Login or Register  to view this content.

[travelkid]

I tried the following:

The goal is that if the T_01 Bo is empty the average function should ignore it.. Why isn't this working here? Is empty not equal to a empty cell in excel?


[Copy to clipboard]

Private Sub T_01_Change()
T_01.Value = Replace(T_01.Value, ",", ".") ' Heizwärmebedarf Wärmeerzeuger 1 2015
'If T_01.Value = "." Then
'T_01a = Val(T_01.Value)

If T_01.Value <> vbNullString Then
T_01a = Val(T_01.Value)
ElseIf T_01.Value = vbNullString Then
T_01a = Empty
End If



T_02a = Val(T_02.Value)

T_13 = WorksheetFunction.Average(T_01a, T_02a)

T_14 = T_01.Value
End Sub

[CK76]

Use "Null" as I have shown in the example.

[travelkid]

Its just not doing the job.

Please Login or Register  to view this content.

The IsNull() Test gives me back False..... so there is something wrong.

[CK76]

Please Login or Register  to view this content.

Above logic is the issue. Should be...

Please Login or Register  to view this content.

[travelkid]

Hello, I have uploaded another worksheet, with what my question can be dealt better I think.

Problem: You can pick a line from the list box and change the values in the text boxes, and then the mean should adapt to these changes. However it still does not ignore zeros... also I have uploaded pictures describing the problem

[CK76]

Hmm, interesting... when I tested, null is only ignored if it is the LAST value in Application.Average(). Otherwise it's evaluated as zero. There must be some internal sorting logic going on for Average calculation when using Range object.

Ex:

Please Login or Register  to view this content.

Returns 7.

Where as...

Please Login or Register  to view this content.

Returns 4.666666...7

I guess, it's easier to just write UDF to handle average calculation for your purpose. I'm going into a meeting now, will post UDF once I'm out.

[CK76]

Put this is standard module. Change UserForm1 as needed.

Please Login or Register  to view this content.

Then use it in your Userform1 like below.

Please Login or Register  to view this content.

[travelkid]

OKey thanks, this is working. I have got a similar problem somwhere else in my project. The difference there is that, the names of my text boxes are T_01, T_05,T_07 and I would like to average them. How would this funciton look like then?

[CK76]

Loop through each control object in form then check for the name.
Ex:

Please Login or Register  to view this content.