Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: IF/AND statements with VBA in XL97

  1. #1
    Guest

    Default

    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.

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  3. #3
    Guest

    Default

    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.





Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •