+ Reply to Thread
Results 1 to 3 of 3

VBA Code If Cell Equals Text Then Assign Value and Sum

  1. #1
    Registered User
    Join Date
    08-13-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    5

    VBA Code If Cell Equals Text Then Assign Value and Sum

    Hello,

    I'm very new to VBA code writing and so I thought I'd get some assistance from this forum. I'm trying to write a VBA code wherein if certain cells contain specific text, it equates the text to a value and then adds them altogether into 1 cell.

    Basically, I have a worksheet that looks like this

    A1 = No
    B1 = Yes
    C1 = 0
    D1 = Yes
    E1 = Good
    F1 = Fair
    G1 = Good
    H1 = 0
    I1 = Poor

    I need the VBA code in cell J1 to state that If text = No or Poor, then equate No and Poor to the value 5, if text = Fair, then equate value to 3, and if text = Yes or Good, value =1, then sum them all up. The cells with 0 in them need to be ignored. Therefore in Cell J1 under the example above, this should equate to a final value of 17 (A1 = 5, B1 =1, C1 (INGNORED), D1 = 1, E1 = 1, F1 = 3, G1 = 1, H1 (IGNORED), I1 = 5 for a total of 17)

    If someone could please assist me in writing the VBA code for this, it would be greatly appreciated.

    Thank you.

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    5,816

    Re: VBA Code If Cell Equals Text Then Assign Value and Sum

    Try:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    01-12-2012
    Location
    UK
    MS-Off Ver
    Excel 2007/10
    Posts
    31

    Re: VBA Code If Cell Equals Text Then Assign Value and Sum

    You're looking at a FOR loop and The SELECT CASE command, i think:

    Please Login or Register  to view this content.
    You could turn this into a function that accepts an input Range object to make things a little more flexible

    If you can live with values in the 2nd row, you can avoid VBA entirely (which is usually good). By having a nested if statement below each cell (or a lookup table if things get complicated)

    A2 could contain "=IF(OR(A1="No",A1="Poor"),5,IF(OR(A1="Yes",A1="Good"),1,IF(A1="Fair",3,0)))", which could be replicated across to I2 and this range summed.

    Cheers

    Mat

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1