Comparing cell values

[Touk]

How do I use VBA to compare data in two cells to return
something. Eg. Column B has numbers,but no zero values,
and Column C has numbers but can contain blank cells.
How do I write code to say if it has numbers in Column C
then to do a calculation that says if column C - Column B
< + 1 or > - 1, then in column D type "Within Range"
else "out of range".

 

[John Pritchard]

Hi there,
Try entering this formula in column D:-

= IF(AND(ISNUMBER(B1),ISNUMBER(C1)),IF(OR(C1-B1 > 1,C1-B1
< -1),"Out of Range","In Range"),"Not both Numbers")

My guess is you wanted numbers that are within +/- 1 of
each other.

 

[Guest]

Excellent!!

How do I write VBA to input that formula in Column D and
filter down to the end of the data that exists in column B?

 

[John Pritchard]

1. Generally users just drag the formula - grab the
little black box on the bottom right of the cell & drag.
The column/row refs change automatically.
2. To enter a formula in VBA just make the cell contain
the formula e.g.

Activesheet.cells(<row>,<col>) = "=IF( etc ...)"

You'll probably want to put this in a while loop -
something like :-

RowCnt = 1

While ActiveSheet.Cells(RowCnt,2) <> ""
<Add the formula>
RowCnt = RowCnt + 1
Wend

3. You could do it all in VBA - Lookup the while and if
statements in the VBA help. If you really are wanting to
do it all in VBA then it's probably best not to bother
with the formula - just do the if and populate column "D"
directly.

Good Luck!