Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Check Each Cell In Range For Value & Put Text In Offset Cell

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Check Each Cell In Range For Value & Put Text In Offset Cell

    Hello,

    I'm trying to look at a value in one column (Column A) and if it matches a set of criteria, I'm trying to change the value of another cell 6 columns to the right.(Column G)
    I've been trying for a couple of days now to get this working, but to no avail. I've tried to modify the code below that I've found on this site but can't quite figure out what I'm doing wrong.
    Code:
    Sub RatingsFix1SP()
         
        Dim FindWhat, rngCell As Range, i As Integer
         
        FindWhat = Array("BB", "B", "CCC", "CC", "C", "CCC+")
        For i = 0 To 3
            For Each rngCell In Range("A2", Range("A" & Rows.Count).End(xlUp))
                If InStr(rngCell, FindWhat(i)) <> 0 Then
                    rngCell.Offset(0, 6) = 0.15
                      End If
            Next rngCell
        Next i
         
    End Sub
    To try to clarify a bit: If column A in the attached spreadsheet has a rating code of BB, B, CCC, CC, C, or CCC+, I want the Haircut % rate in column G to be 15%.

    It seems the code that I'm using changes the % rate to 15% if any "B" or "C" shows up in any part of the string in column A.

    If someone can tell me what I've done wrong or has a better solution, it would certainly be very much appreciated.

    Thanks kindly,
    JL
    Attached Files

  • #2
    Re: Offset Cell Value Based On Criteria

    Hi!

    Your code seems Okay to me...

    One questions though, in the following lines

    Code:
    FindWhat =  Array("BB", "B", "CCC", "CC", "C", "CCC+") 
        For i = 0 To 3
    Why is i set from 0 to 3 if it's to cycle through the array which holds 6 elements?

    Did you set the cell format to be percentage?
    If they say it can't be done, we'll find THE solution at OzGrid's...

    Comment


    • #3
      Re: Offset Cell Value Based On Criteria

      Give this a shot...

      Code:
      Option Explicit
      Sub RatingsFixed1sp()
      
      Dim rngBond As Range
      Dim rngCell
      
      Set rngBond = Range("A2", Range("A65536").End(xlUp))
       For Each rngCell In rngBond
      Select Case rngCell
          Case "BB", "B", "CCC", "CC", "C", "CCC+"
             rngCell.Offset(0, 6) = 0.15
         End Select
       Next rngCell
      
      End Sub
      I'm sure I've cut a few corners with my limited knowledge, but hopefully some of the other fine folk can clean it up and teach us both something.

      Comment


      • #4
        Re: Offset Cell Value Based On Criteria

        Hi [email protected], msoliman

        Thanks much for your input.

        Why is i set from 0 to 3 if it's to cycle through the array which holds 6 elements?
        That's just me not adapting the code I found correctly.
        However, when I changed the line to read from 0 to 6, I got a "subscript out of range" error for the following line:
        Code:
        If InStr(rngCell, FindWhat(i)) <> 0 Then
        No worries though as msoliman's code seemed to do the trick.
        Thanks a bunch msoliman, really appreciate your help!

        Regards,

        JL

        Comment


        • #5
          Re: Offset Cell Value Based On Criteria

          However, when I changed the line to read from 0 to 6, I got a "subscript out of range" error for the following line:
          Since the range starts at 0 and the array holds 6 element, the range must be 0 to 5. (Thus 6 total)
          If they say it can't be done, we'll find THE solution at OzGrid's...

          Comment


          • #6
            Re: Offset Cell Value Based On Criteria

            Ahhhh, I understand that bit now. Thanks [email protected]!

            Still strange that when I run the code below, it changes column G on a partial match. (i.e. any cell in column A containing the letter "B" or a "C" seems to get the .15 value.
            Code:
            Sub RatingsFix1SP()
            
                Dim FindWhat, rngCell As Range, i As Integer
                 
                FindWhat = Array("BB", "B", "CCC", "CC", "C", "CCC+")
                For i = 0 To 5
                    For Each rngCell In Range("A2", Range("A" & Rows.Count).End(xlUp))
                        If InStr(rngCell, FindWhat(i)) <> 0 Then
                            rngCell.Offset(0, 6) = 0.15
                              End If
                    Next rngCell
                Next i
                 
            End Sub
            Again, msoliman's code worked for my purposes.

            Thanks again for your help.

            JL

            Comment

            Trending

            Collapse

            There are no results that meet this criteria.

            Working...
            X