IF/AND statements with VBA in XL97

Hello. I have a problem that I can't seem to figure out.

I am using a userform through VBA to insert a value into a cell. In an adjacent cell I use a if/and statement to determine whether or not the number fits into certain ranges. Depending on the number, the cell is given a text value. The following is an example:

In cell A1:
1.8 (enter there though the userform)

In cell A2:

=IF(AND(0.002>A1, A1>0),"CLAY",IF(AND(0.074>A1, A1>0.002),"SILT",IF(AND(5>A1,A1>0.074),"SAND",IF(AND(A1>=5,"GRAVEL","ERROR!!!"))))

Before running the VBA code, cell A1 has no value (is blank), and thus cell A2 shows 'ERROR!!!'

But, when I run the VBA code and insert the number into A1 (thus updating the cell), cell A2 does not update! It continues to show 'ERROR!!!'. Now, if I manually enter the value afterwards, it changes to the proper text (ie. if A1=1.8, A2 = "SAND").

I'm stuck as to how to make the text in A2 update when A1 is changed through coding. I know that formulas/calculations automatically change when a refering cell is changed, but does the same apply to if/and functions?

Thanks a bunch for any help.
Corey D.

[Dave Hawley]

Hi

There is no reason why your formula shoudn't update, as long as you have Calculation in Auto! You could use:
Range("A2").Calculate
To force a calculation, but you have a problem elsewhere!

I figured out my problem.

When I was setting the cell to be equal to the value from the textbox in my userform, I didn't use the Val statement to make sure it was pasting it as a number and not text.

This didn't work:

Range("A1").Select
Selection.Value = EnteredNumber

But this did:

Range("A1").Select
Selection.Value = Val(EnteredNumber)

Thanks for taking the time to read and check the formula!

Regards,
Corey D.