Writing an IF/Then Statement in VB based on entry in a cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Barb Reinhardt - 23 Mar 2006 17:26 GMT

I have defined the following value for a cell within my macro

Range("K" & i).Select
ActiveCell.FormulaR1C1 = _
"=((YEAR(RC[-1])-YEAR(RC[-2]))*12)+(MONTH(RC[-1])-MONTH(RC[-2]))"

If the value of this range is > 0, I want to do other things. I'm not sure
how to write an "IF" statement to capture this information. How do I do
that?

Thanks in advance,
Barb Reinhardt

Reply to this Message

bigwheel - 23 Mar 2006 17:59 GMT

Something like this perhaps:-

If ActiveCell.Value > 0 Then
' put here what you want to happen i.e.
MsgBox "it's working!!"
End If

> I have defined the following value for a cell within my macro
>
[quoted text clipped - 8 lines]
> Thanks in advance,
> Barb Reinhardt

Reply to this Message

Barb Reinhardt - 24 Mar 2006 14:56 GMT

Thanks. The ActiveCell.Value part was what I needed.

Now I have another question. How do I check to see If ActiveCell.Value =
#N/A. I can't seem to figure this one out.

> Something like this perhaps:-
>
[quoted text clipped - 15 lines]
> > Thanks in advance,
> > Barb Reinhardt

Reply to this Message

Chip Pearson - 24 Mar 2006 15:04 GMT

Barb,

> Now I have another question. How do I check to see If
> ActiveCell.Value =
> #N/A. I can't seem to figure this one out.

Try

If Application.WorksheetFunction.IsNA(ActiveCell.Value) Then
Debug.Print "is n/a"
Else
Debug.Print "not n/a"
End If

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

> Thanks. The ActiveCell.Value part was what I needed.
>
[quoted text clipped - 25 lines]
>> > Thanks in advance,
>> > Barb Reinhardt

Reply to this Message

Dave Peterson - 24 Mar 2006 15:19 GMT

One more way:

if activecell.text = "#N/A" then

> Thanks. The ActiveCell.Value part was what I needed.
>
[quoted text clipped - 20 lines]
> > > Thanks in advance,
> > > Barb Reinhardt

Signature

Dave Peterson

Reply to this Message

Barb Reinhardt - 24 Mar 2006 16:29 GMT

How about if the cell value is #Value! ???

> One more way:
>
[quoted text clipped - 24 lines]
> > > > Thanks in advance,
> > > > Barb Reinhardt

Reply to this Message

Dave Peterson - 24 Mar 2006 16:49 GMT

if activecell.text = "#Value!" then

or

if iserror(activecell.value) then
to catch all kinds of errors

> How about if the cell value is #Value! ???
>
[quoted text clipped - 30 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Barb Reinhardt - 24 Mar 2006 17:06 GMT

For some reason, it didn't work as I expected ... and then I realized I'd
goofed something else up. Thanks.

> if activecell.text = "#Value!" then
>
[quoted text clipped - 37 lines]
> > >
> > > Dave Peterson

Reply to this Message

Tom Hutchins - 23 Mar 2006 17:59 GMT

Range("K" & i).Activate
ActiveCell.FormulaR1C1 = _
"=((YEAR(RC[-1])-YEAR(RC[-2]))*12)+(MONTH(RC[-1])-MONTH(RC[-2]))"
ActiveCell.Value > 0 Then
'do something
Else
'do something else
End If

Hutch
--------------------------------

> I have defined the following value for a cell within my macro
>
[quoted text clipped - 8 lines]
> Thanks in advance,
> Barb Reinhardt

Reply to this Message