VBA
 

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.

VBA
 

Steve,

Look at Conditional Formatting with Cell Value ="#REF!"

--
sb
"Steved" wrote in message
...
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.

VBA
 

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.
--
sb
"Steved" wrote in message
...
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.

VBA
 

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)}


-----Original Message-----
Steve,

Look at Conditional Formatting with Cell Value ="#REF!"

--
sb
"Steved" wrote in
message
...
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.


.

VBA
 

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.

--
sb
"Steved" wrote in message
...
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)}


-----Original Message-----
Steve,

Look at Conditional Formatting with Cell Value ="#REF!"

--
sb
"Steved" wrote in
message
...
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.


.

VBA
 

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)}


-----Original Message-----
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.

--
sb
"Steved" wrote in
message
...
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)}


-----Original Message-----
Steve,

Look at Conditional Formatting with Cell Value ="#REF!"

--
sb
"Steved" wrote in
message
...
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.


.



.

VBA
 

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)}


-----Original Message-----
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.

--
sb
"Steved" wrote in
message
...
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)}


-----Original Message-----
Steve,

Look at Conditional Formatting with Cell Value ="#REF!"

--
sb
"Steved" wrote in
message
...
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.


.



.

VBA
 

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:C1 00,0))

Also I think you are still missing part of the Small function =
Small(Array,k)
where k is the level (1, 2, 3, .....)
--
sb
"Steved" wrote in message
...
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)}


-----Original Message-----
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.

--
sb
"Steved" wrote in
message
...
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)}


-----Original Message-----
Steve,

Look at Conditional Formatting with Cell Value ="#REF!"

--
sb
"Steved" wrote in
message
...
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.


.



.

VBA
 

From Steved

Cheers for your time

I will go peice by peice as you suggest.

Thankyou.

-----Original Message-----
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, .....)
--
sb
"Steved" wrote in
message
...
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)}


-----Original Message-----
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.

--
sb
"Steved" wrote in
message
...
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)}


-----Original Message-----
Steve,

Look at Conditional Formatting with Cell Value
="#REF!"

--
sb
"Steved"
wrote in
message
...
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.


.



.



.

VBA
 

Steve,

Best of Luck!

Post back with what you find...

--
sb
"Steved" wrote in message
...
From Steved

Cheers for your time

I will go peice by peice as you suggest.

Thankyou.

-----Original Message-----
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, .....)
--
sb
"Steved" wrote in
message
...
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)}


-----Original Message-----
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.

--
sb
"Steved" wrote in
message
...
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)}


-----Original Message-----
Steve,

Look at Conditional Formatting with Cell Value
="#REF!"

--
sb
"Steved"
wrote in
message
...
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.


.



.



.