+ Reply to Thread
Results 1 to 8 of 8

If Then Macro based on one column to assign category to another

Hybrid View

  1. #1
    Registered User
    Join Date
    05-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    43

    If Then Macro based on one column to assign category to another

    I have a report I am trying to assign per deal in VBA as "excess" or as "liability" in Column X based on a value in Column S:
    If cell in column S is > 99.99 then it equals "EXCESS" in Column X
    If cell in Column S is < 99.99 then it equals "LIABILITY" in Column X

    The thing I am struggling with is assigning the value in Column X from Column S starting at cell S11 since that is where the values start. The header starts at cell S10 so anything below that with value applies.

    Let me know if you can help!

  2. #2
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: If Then Macro based on one column to assign category to another

    Formula:

    =IF(S11 > 99.99, "EXCESS", "LIABILITY")

    VBA:

    Sub angeleenmc()
    
    For Each Cell In Range("S11:S" & Cells(Rows.Count, "S").End(xlUp).Row)
        If Cell.Value > 99.99 Then
            Range("X" & Cell.Row).Value = "EXCESS"
        Else
            Range("X" & Cell.Row).Value = "LIABILITY"
        End If
    Next
    
    End Sub

  3. #3
    Registered User
    Join Date
    05-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    43

    Re: If Then Macro based on one column to assign category to another

    Thank you!
    Can you also help me with another macro? If Column P contains "A/", "A.", "U/" or "UD/" as the first 2 or 3 characters of the cell to the left then it equals "PAID" or "AD" in Column Z

  4. #4
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: If Then Macro based on one column to assign category to another

    as the first 2 or 3 characters of the cell to the left
    I'm not understanding how the cell to the left (column O, i guess?) is related to this problem. I read it as: if column P starts with certain letters, modify column Z.

  5. #5
    Registered User
    Join Date
    05-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    43

    Re: If Then Macro based on one column to assign category to another

    If a cell in Column P contains "A/", "A.", "U/" or "UD/" in the beginning of the cell then Column Z = AD, if not then = PAID in Column Z

  6. #6
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: If Then Macro based on one column to assign category to another

    Sub angeleenmc2()
    Arr = Array("A/", "A.", "U/")
    For Each Cell In Range("P11:P" & Cells(Rows.Count, "P").End(xlUp).Row)
        If UBound(Filter(Arr, Left(Cell.Value, 2))) > -1 Or Left(Cell.Value, 3) = "UD/" Then
            Range("Z" & Cell.Row).Value = "AD"
        Else
            Range("Z" & Cell.Row).Value = "PAID"
        End If
    Next
    End Sub

  7. #7
    Registered User
    Join Date
    05-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    43

    Re: If Then Macro based on one column to assign category to another

    Thank you!

  8. #8
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: If Then Macro based on one column to assign category to another

    Glad to help. Please mark this thread as SOLVED.

    And if your satisfied with any of the solutions in this thread, reputation points (the * in the lower left of my posts) are appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Assign a category to a cell based on two conditions
    By jeffepro in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-09-2015, 07:38 AM
  2. Replies: 2
    Last Post: 01-07-2015, 09:06 AM
  3. Replies: 15
    Last Post: 10-12-2014, 08:53 AM
  4. [SOLVED] How Do I Assign Attributes to Entries Based on Category?
    By kelman17 in forum Excel General
    Replies: 6
    Last Post: 06-10-2013, 09:31 PM
  5. [SOLVED] Sum parts of a column based on a category from another column
    By Matchboxx in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-27-2012, 07:43 AM

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