Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

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

1. Senior Member
Join Date
18th February 2004
Location
Florida
Posts
216

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

Excel Video Tutorials / Excel Dashboards Reports

2. ## 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?

3. ## 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. Senior Member
Join Date
18th February 2004
Location
Florida
Posts
216

## Re: Offset Cell Value Based On Criteria

Hi [email protected], msoliman

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

6. Senior Member
Join Date
18th February 2004
Location
Florida
Posts
216

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

JL

Excel Video Tutorials / Excel Dashboards Reports

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

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