Macro subprocedure

  • Thread starter Thread starter orquidea
  • Start date Start date
O

orquidea

Hi All

Could anyone please help me to write a subprocedure wich replace the cell
with the error note #VALUE! with the comment "No Info"
I have the below

If ActiveCell.Value = numero (I don' know how to tell the computer
that any number is valid) Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Value = "No Info"
End If

Thanks,
Orquidea
 
You can easily accomplish this without using a macro... in the column or rows
where you recieve the #VALUE error try using an If(Iserror) statement. So if
you have a formula like

=A1/B1

If B1 is 0 you will get an error... but to fight that you would put

=If(Iserror(A1/B1),"No Info",A1/B1)

So basically you are just wrapping your current formula in the Iserror(). So
if an error pops up it will write in "No Info"
 
Thanks for your reply. The problem is that I am using the formula
=NETWORKDAYS(G8,H8). Then a macro will copy this formula all the way down in
the column until there is data available. In some rows, there G or H is
empty and then I get the error mesage. I need to use a macro. Could you
help me with ti please
 
You want to post the macro?

Also try

=If(Iserror(Networkdays(G8,H8),"No Info",Networkdays(G8,H8))
 
Yes, I tried you formula with NETWORKDAYS and worked. I am trying to put
that into a macro. Let see.
Below is the original macro I did.

Range("O2").Select
Do
If ActiveCell.Value = "#VALUE!" Then
ActiveCell.Value = "No Info"
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Offset(0, -13) = ""


I also would like to know how could I say
If ActiveCell.Value = any number then
ActiveCell.Offset(1,0)

Thanks
Orquidea
 
You can also use the IsError formula in code... so your macro can be

Range("O2").Select
Do Until ActiveCell.Offset(0, -13) = ""
If IsError(ActiveCell.Value) Then
ActiveCell.Value = "No Info"
End If
ActiveCell.Offset(1,0).Activate
Loop

To check for numbers you can use

If IsNumeric
 
Thanks for your help. It worked.

akphidelt said:
You can also use the IsError formula in code... so your macro can be

Range("O2").Select
Do Until ActiveCell.Offset(0, -13) = ""
If IsError(ActiveCell.Value) Then
ActiveCell.Value = "No Info"
End If
ActiveCell.Offset(1,0).Activate
Loop

To check for numbers you can use

If IsNumeric
Click to expand...
 
Back
Top