Hey all,

Need some assistance with a macro I am currently trying to formulate. I ususally use AutoFilter or IF Statements, so I am totally new to Case Select and need some basic help. To give some background info on what I'm working on, I have a file with various data. I need to create a Case Select to go through each row so to go from A2 (Since A1 is header) till the the last cell (End(xlDown) but Idk how to do that with Case Select) and then do something like the following:

• If Q2 = "Cash" AND J2 = "GC" then change cell A2 to "Capital Activity"
• If Q2 = "Bond Deals" AND B2 contains "CLO" (don't know how to do this in case select either) then change cell A2 to "CLO Subtype"
• If Q2 = "Bond Deals" AND R2 has 9 or 12 characters (don't know how to do this in case select either..use LEN?) then change cell A2 to "Bond"

If anyone can provide me with that I can figure out the rest of my macro on my own. I have provided my previous macro which got too messy/big/complicated which I tried to do the same thing using AutoFilter. After talking to one of my peers, he advised I look into using Case Select so that is why I am asking. Thanks in advance. I have also provided the file for viewing/using.

For The Q2 = Cash & J2 = "GC" > A2: Capital Activity

'Capital Activity Search
    ActiveSheet.range(Selection, Selection.End(xlUp)).AutoFilter Field:=7, Criteria1:="GC"
    range("B1").Select
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.Height <> 0
    ActiveCell.Offset(1, 0).Select
    Loop
    If Not Selection.Offset(0, 1) = "" Then
    Selection.End(xlToLeft).Select
    Selection.End(xlDown).Select
    ActiveCell.FormulaR1C1 = "Capital Activity"
    Selection.Copy
    range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Selection.End(xlUp).Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "'Subtype"
    End If
    ActiveSheet.ShowAllData

For The Q2 = Bond Deals & B2 containing "CLO" > A2: CLO Subtype

  ActiveSheet.range(Selection, Selection.End(xlUp)).AutoFilter Field:=12, Criteria1:="BondsDeals"
  range("C1").Select
    ActiveSheet.range(Selection, Selection.End(xlUp)).AutoFilter Field:=3, Criteria1:="=*CLO *"
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.Height <> 0
    ActiveCell.Offset(1, 0).Select
    Loop
    If Not Selection.Offset(1, 0) = "" Then
    Selection.End(xlToLeft).Select
    Selection.End(xlDown).Select
    ActiveCell.FormulaR1C1 = "CLO Subtype"
    Selection.Copy
    range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Selection.End(xlUp).Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "'Subtype"
    End If
    ActiveSheet.ShowAllData

As you can see, my code is very messy and complicated. I would really appreciate help, and am more than ready to provide more information if needed. Here is the example file. Thanks In Advance!

https://www.dropbox.com/s/21xhnd8dz5kr9o5/CaseSelectEG.xlsx?m

I would not use Select Case. It's OK to just use IF / THEN statements- Select Case performs the same function but is easier to read and may process faster:

    If Range("Q2").Value = "Cash" And Range("J2").Value = "GC" Then
        Range("A2").Value = "Capital Activity"
    End If
    If Range("Q2").Value = "Bond Deals" And Range("B2").Value Like "*CLO*" Then
        Range("A2").Value = "CLO Subtype"
    End If
    If Range("Q2").Value = "Bond Deals" And ((Len(Range("R2").Value) = 9) Or (Len(Range("R2").Value) = 12)) Then
        Range("A2").Value = "Bond"
    End If

Though it seems like you need to account for the case where B2 has CLO and R2 is 9 or 12 characters long (unless that is impossible)  - perhaps CLO is dominant, then

    If Range("Q2").Value = "Cash" And Range("J2").Value = "GC" Then
        Range("A2").Value = "Capital Activity"
    End If
    If Range("Q2").Value = "Bond Deals" Then
        If Range("B2").Value Like "*CLO*" Then
            Range("A2").Value = "CLO Subtype"
        ElseIf ((Len(Range("R2").Value) = 9) Or (Len(Range("R2").Value) = 12)) Then
            Range("A2").Value = "Bond"
        End If
    End If

You may want to initialize A2 to make sure that the old value does not stick around, like this as the first line:

Range("A2").Value = "Nothing Yet"

 

I would not use Select Case. It's OK to just use IF / THEN statements- Select Case performs the same function but is easier to read and may process faster:

    If Range("Q2").Value = "Cash" And Range("J2").Value = "GC" Then
        Range("A2").Value = "Capital Activity"
    End If
    If Range("Q2").Value = "Bond Deals" And Range("B2").Value Like "*CLO*" Then
        Range("A2").Value = "CLO Subtype"
    End If
    If Range("Q2").Value = "Bond Deals" And ((Len(Range("R2").Value) = 9) Or (Len(Range("R2").Value) = 12)) Then
        Range("A2").Value = "Bond"
    End If

Though it seems like you need to account for the case where B2 has CLO and R2 is 9 or 12 characters long (unless that is impossible)  - perhaps CLO is dominant, then

    If Range("Q2").Value = "Cash" And Range("J2").Value = "GC" Then
        Range("A2").Value = "Capital Activity"
    End If
    If Range("Q2").Value = "Bond Deals" Then
        If Range("B2").Value Like "*CLO*" Then
            Range("A2").Value = "CLO Subtype"
        ElseIf ((Len(Range("R2").Value) = 9) Or (Len(Range("R2").Value) = 12)) Then
            Range("A2").Value = "Bond"
        End If
    End If

You may want to initialize A2 to make sure that the old value does not stick around, like this as the first line:

Range("A2").Value = "Nothing Yet"

 

Thank you for your advice Bernie again! I looked into using If Statements and have the current macro which seems to be working but has one error. When it runs, everything works except the second If statement (Value Like "*CLO*". It works fine when the comment does indeed contain "CLO" in it...but if the comment is "#N/A" and error occurs. Anyway you can help me fix this?

Dim cl as Range

  For Each cl in Range("A2", Range("A" & Rows.Count).End(xlUp))

     If cl.Offset(,16).Value = "Cash" And cl.Offset(,9).Value = "GC" Then
           cl.Value ="Capital Activity"
     End If

     If cl.Offset(,16).Value = "Bond Deals" And cl.Offset(,1).Value Like "*CLO*" Then
           cl.Value = "CLO Subtype"
     End If

     If cl.Offset(,16).Value = "Bond Deals" And (Len(cl.offset(,17).Value) = 9 Or Len(cl.offset(,17).Value) = 12) Then
           cl.Value = "Bond"
     End If

  Next cl

EDIT: I realized what I need is a simple line of code at the start of my macro which would simply automatically convert all the data at the start of the macro into the values which the formulas have already come up with...any way you can help with this?

If you can have error values, you need one or more checks. For example, change

    If cl.Offset(, 16).Value = "Cash" And cl.Offset(, 9).Value = "GC" Then
        cl.Value = "Capital Activity"
    End If

to

If (Not IsError(cl.Offset(, 16).Value)) And (Not IsError(cl.Offset(, 9).Value)) Then
    If cl.Offset(, 16).Value = "Cash" And cl.Offset(, 9).Value = "GC" Then
        cl.Value = "Capital Activity"
    End If
End If

or just this if only J can have errors

If Not IsError(cl.Offset(, 9).Value) Then
    If cl.Offset(, 16).Value = "Cash" And cl.Offset(, 9).Value = "GC" Then
        cl.Value = "Capital Activity"
    End If
End If

Have figured everything out pretty much just need help one part of it. The
current problem is that there are some errors from formulas that display "#N/A"
is there anyway to convert this into a simple "NA" so it's a string and not an
error? Thanks in advance.

Cells.SpecialCells(xlCellTypeFormulas, 16).Value = "NA"

will replace all error values in all cells of the active sheet with NA. Formulas that return non-error values will not be affected.

Or - change all your formulas to

=IF(ISERROR(oldFormula without = sign),"NA", old formula)

You could try something like the code below.  Note that instead of selecting the range to process by doing (xlDown), I go to the bottom and then come up.  This takes into account the possibility of blanks in column Q.

I only interpreted the specifications you provided, and tried to write the code so it would be clear (self-documenting).

Also note that, with regard to "Bond Deals", the Len(column R) takes precedence over "containing "CLO"".  If that is not what you want, then merely reverse the order of those two IF statements.

Finally, if the code runs slowly (because of the size of the data table), it can be sped up considerably by reading the entire table into an array; processing the array; and then writing the array back (or just writing back column "A") to the worksheet.

Option Explicit
Option Compare Text 'to make case INsensitive
    'If Q2 = "Cash" AND J2 = "GC" then change cell A2 to "Capital Activity"
    'If Q2 = "Bond Deals" AND B2 contains "CLO" (don't know how to do this in case select either) then change cell A2 to "CLO Subtype"
    'If Q2 = "Bond Deals" AND R2 has 9 or 12 characters (don't know how to do this in case select either..use LEN?) then change cell A2 to "Bond"
Sub foo()

Dim r As Range
Dim rw As Range

Set r = Range("Q2", Cells(Rows.Count, "Q").End(xlUp))
    For Each rw In r.Rows.EntireRow
      With rw
        Select Case .Cells(1, "Q")
            Case "Cash"
                If .Cells(1, "J") = "GC" Then .Cells(1, "A") = "Capital Activity"
            Case "Bond Deals"
                If InStr(.Cells(1, "B"), "CLO") > 0 Then .Cells(1, "A") = "CLO Subtype"
                If Len(.Cells(1, "R")) = 9 Or Len(.Cells(1, "R")) = 12 Then .Cells(1, "A") = "Bond"
            Case Else
                'you might want to consider other contents of column Q
        End Select
      End With
    Next rw
End Sub		

Cells.SpecialCells(xlCellTypeFormulas, 16).Value = "NA"

will replace all error values in all cells of the active sheet with NA. Formulas that return non-error values will not be affected.

Or - change all your formulas to

=IF(ISERROR(oldFormula without = sign),"NA", old formula)

Thank you, I've tried to edit my macro but evertything except one thing works. I am trying to add an another "If" criteria to make sure that every single line is only checking lines with it's subtype (column A) as "#N/A". Is there anyway to have this done? This is what I have written out to "try".

     If cl.Value = "#N/A" And cl.Offset(, 11).Value = "Bond Deals" And cl.Offset(, 2).Value Like "*CLO*" Then
            cl.Value = "CLO"
     End If

or

     If cl.Offset(0, 0).Value = "#N/A" And cl.Offset(, 11).Value = "Bond Deals" And cl.Offset(, 2).Value Like "*CLO*" Then
            cl.Value = "CLO"
     End If

If there is any workaround this, please let me know.

Replace
If cl.Value = "#N/A"
with

If cl.Text = "#N/A"

or

If Application.WorksheetFunction.IsNA(cl)