VBA

  • Thread starter Thread starter Steved
  • Start date Start date
S

Steved

Hello from Steved

I would like to know is it possible in VBA to find #REF!
in any cell in the worksheet and color the text white as
to give the appearence the cell is blank.

Thankyou.
 
Steve,

The vb approach would be to

Go To > Special > Formulas > Errors
format the text color to white.

Record the macro for future use.

Caution: What happens when the cell recalculates to a usable value?
How will you know?

Or record a macro to apply Conditional Formatting per my previous post.
 
Hello from Steved

Thanks for your quick reponse
Below is the formula that returns #REF! if there is no
Data I tried Conditional Formating but I having no luck

{=INDEX(B$1:$C$217,SMALL(IF(B$1:$B$217=$D$17,ROW
($B$1:$B$217)-ROW($B$1)+1,ROW($B$217)+1), $E$20),2)}
 
Steve,

Am having trouble following your formula. But I get #NUM as a result
whether I enter it as an array or normal formula.

Suggest you take it a piece at a time and test it.
Put each piece in a cell as a regular formula.

The #REF usually means that the reference doesn't exist.

Make sure that you follow the standard formats for the formulas.

Small() = Small(Array,k)
I am having trouble with this part of your formula.
 
Hello from Steved

Steve the below formula is based on a complete months data

What happens as today is 22nd October The #REF! changes to
38028 as an example. (1st October to 22nd October) but the
rest of the month will display #REF! in the cells until a
completed month. What I have acheived is to do what you
asked and that is Go To > Special > Formulas > Errors
format the text to white, but as you point out what
happened was when the cell recalculates to a usable value.
So my thinking is that when the cell displays #REF! I
tried to put in {=INDEX(B$1:$C$217,SMALL(IF
(B$1:$B$217=$D$17,ROW ($B$1:$B$217)-ROW($B$1)+1,ROW($B$217)
+1), $E$20),2)=IF(ISERROR(#REF!),"","No Error")}hoping
this would have made the cell go blank but no go.

Have you any thoughts

Cheers


{=INDEX(B$1:$C$217,SMALL(IF(B$1:$B$217=$D$17,ROW
($B$1:$B$217)-ROW($B$1)+1,ROW($B$217)+1), $E$20),2)}
 
Hello from Steved

Steve the below formula is based on a complete months data

What happens as today is 22nd October The #REF! changes to
38028 as an example. (1st October to 22nd October) but the
rest of the month will display #REF! in the cells until a
completed month. What I have acheived is to do what you
asked and that is Go To > Special > Formulas > Errors
format the text to white, but as you point out what
happened was when the cell recalculates to a usable value.
So my thinking is that when the cell displays #REF! I
tried to put in {=INDEX(B$1:$C$217,SMALL(IF
(B$1:$B$217=$D$17,ROW ($B$1:$B$217)-ROW($B$1)+1,ROW($B$217)
+1), $E$20),2)=IF(ISERROR(#REF!),"","No Error")}hoping
this would have made the cell go blank but no go.

Have you any thoughts

Cheers


{=INDEX(B$1:$C$217,SMALL(IF(B$1:$B$217=$D$17,ROW
($B$1:$B$217)-ROW($B$1)+1,ROW($B$217)+1), $E$20),2)}
 
Steve,

The best way would be a simple if formula based on the part of the major
formula. So figure out which piece errs than
=If(IsError(*minor piece*),"",*major piece*)

Also - are you sure you need an array formula?

You don't want to use 'IsError(#REF!)' but rather something like
=If(IsError(Match(A1,B1:C100,0)),"",Match(A1,B1:C100,0))

Also I think you are still missing part of the Small function =
Small(Array,k)
where k is the level (1, 2, 3, .....)
 
Back
Top