Pick value off sheet list for VBA code determination

  • Thread starter Thread starter Bill (Unique as my name)
  • Start date Start date
B

Bill (Unique as my name)

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