Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

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

  1. #1
    Join Date
    18th February 2004
    Location
    Florida
    Posts
    181

    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.
    VB:
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    29th March 2006
    Location
    Deux-Montagnes, Qc, Canada
    Posts
    50

    Re: Offset Cell Value Based On Criteria

    Hi!

    Your code seems Okay to me...

    One questions though, in the following lines

    VB:
    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...


  3. #3
    Join Date
    27th March 2007
    Location
    Framingham, MA
    Posts
    26

    Re: Offset Cell Value Based On Criteria

    Give this a shot...

    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th February 2004
    Location
    Florida
    Posts
    181

    Re: Offset Cell Value Based On Criteria

    Hi W@rrior, 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:
    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    29th March 2006
    Location
    Deux-Montagnes, Qc, Canada
    Posts
    50

    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...


  6. #6
    Join Date
    18th February 2004
    Location
    Florida
    Posts
    181

    Re: Offset Cell Value Based On Criteria

    Ahhhh, I understand that bit now. Thanks W@rrior!

    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.
    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 7
    Last Post: May 22nd, 2008, 05:27
  2. Offset Cell From Found Maximum In Range
    By chs4 in forum EXCEL HELP
    Replies: 4
    Last Post: February 21st, 2008, 00:32
  3. Find Cell Text, Offset & Name The Cell/Range
    By longhorn14 in forum EXCEL HELP
    Replies: 7
    Last Post: August 27th, 2006, 10:07
  4. Naming Cell Range vs. using ActiveCell.Offset
    By GuyGadois in forum EXCEL HELP
    Replies: 3
    Last Post: January 11th, 2006, 23:54
  5. VBA: using Offset to return a range, not a single cell
    By Vonbloodbath in forum EXCEL HELP
    Replies: 2
    Last Post: June 21st, 2005, 00:50

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
  •  
porno