Pick value off sheet list for VBA code determination
 

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

Pick value off sheet list for VBA code determination
 

Bill,

Don't loop. Create your list of values, name it ValueList, and then run this macro. Assumes that
the range where you are drawing the .Value from are the cells A1:A100.


Sub BillValues()

Dim myR As Range

Set myR = Range("A1:A100")

With myR.Offset(0, 1)
.FormulaR1C1 = _
"=IF(ISERROR(MATCH(RC[-1],ValueList,FALSE)),"""",""MP"")"
.Value = .Value
End With

End Sub

HTH,
Bernie
MS Excel MVP


"Bill (Unique as my name)" wrote in message
ups.com...
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 the code 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.