Evaluating cells for items in list

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I am looking for some help with code to evaluate a cell.
When I evaluate a cell, if it contains one of 15 accounts,
I have to evaluate another cell (same row, column b) to be
sure it (the second cell) is not empty. If it (the second
cell) is empty, then a message will be displayed in column
K.

The part I'm having trouble with is the syntax to see if
the first cell (account) is either 101, 102, 105, 107..etc.

I could string together a bunch of If / Or statements but
there must be a better way. Thanks for the help.
 
Two alternatives:
1. hardcoding the accounts (faster to run, less flexible):
dim acct(15)
acct(1) = 101
acct(2) = 102
acct(3) = 105
....
acct(15) = ...

for i = 1 to 15
if range(evaluated cell).value = acct(1) then
'code to do what I want
exit for
end if
next


2. keeping the target accounts in another sheet,
say "Lookup", range A1:A15 (slower in execution, much more
flexible in changing target accounts and / or number
thereof):

chk = range(evaluated cell).value
sheets("Lookup").select
range("A1").select
do while isempty(activecell) = false
if activecell.value = chk then
'code to do what I want
exit do
end if
loop

Nikos
 
Back
Top