Using VBA to Enter Data into a Cell produces #DIV/0! Error

[paulrockliff]

Afternoon People,

I have a spreadsheet that uses Userforms to enter data into some of my cells. I am having a number of problems with formulas that perform calculations based on these cells not working.

For example I have used the code:

Sheet3.Range("E8") = UserForm6.TextBox1

To copy a three or four digit number to Cell E3 of a sheet. I've used identical code to copy other values to other cells and then I have this formula in another cell on another sheet:

=IF(AB41="","",AVERAGE('Data Entry'!E13:FJ13))

This cell returns a #DIV/0! error, but if the numbers contained within the range it is trying to average are typed in manually the average formula works perfectly.

I can't find any difference between the contents of the cells in wither case and it isn't an issue with the sheet not recalculating.

Can anyone please explain what is happening and how I go about making ot work properly?

Thanks

Paul.

[mikerickson]

A text box's value is a string.
[VBA]Sheet3.Range("E8") = UserForm6.TextBox1[/VBA]is putting a string in cell E8.

Excel's automatic routines might read that as a number.
I don't trust automatic data conversion routines.
I'd try code like
[VBA]Sheet3.Range("E8") = Val(UserForm6.TextBox1)
[/VBA]