Pick value off sheet list for VBA code determination

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill (Unique as my name) - 21 Sep 2007 17:22 GMT

Apologies for the cross post.

Here is a sample of VBA code used to place a value in a cell base on
the value entered in the cell previous

If LCase(.Value) = "capital" OR _
LCase(.Value) = "grounds" OR _

(etc., for many lines)

.Offset(0, 1).Value = "MP"

The code works fine. Now I want to refer to a list on a sheet to
determine the value of a cell, i.e., if the value in a cell equals an
item from the sheet list, then a value is assigned to another cell.

Is this possible?

Thank you in advance.

Reply to this Message

FSt1 - 21 Sep 2007 18:02 GMT

hi
i did a little test and it works
Sub Test()
Dim c As Range
Dim r As Range
Set r = ActiveCell
For Each c In Range("J1:J5") ' your list?
If c.Value = r.Value Then
ActiveCell.Offset(0, 1).Value = "gotit"
Exit For
End If
Next
End Sub

it should give you the basic idea. edit to fit your code.

> Apologies for the cross post.
>
[quoted text clipped - 15 lines]
>
> Thank you in advance.

Reply to this Message

Bill (Unique as my name) - 21 Sep 2007 19:10 GMT

> hi
> i did a little test and it works
[quoted text clipped - 31 lines]
>
> > Thank you in advance.

Thanks FSt1. So beautiful. So Concise.

Reply to this Message