IF/AND statements with VBA in XL97

G

Guest

Guest
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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top