VBA controlled conditional format
 

Hi,

I'm using a small script to copy a formula down to certain cells based on an
option button selection by the user. For example (this is the code for one
of the option buttons)

Private Sub Opt20_Click()
Dim wks
Set wks = Worksheets("Interface")
wks.Range("f13:f1000").ClearContents
For x = 13 To 32
AccDesc = "=VLOOKUP($E$" & x & " ,[Data.xls]SAP_COA_Map!$D:$E,2)"
wks.Range("f" & x) = AccDesc
Next x
Set wks = Nothing
End Sub

When the user selects this button the formula is copied down and each cell
is calculated and a value is return. The problem here is that the user will
select the button before anything is entered into cell range E:E with the
result that the cells displays "#N/A" because the formula doesn't have
anything to work with.

Using code how do I hide the result in the cell if it is #N/A

Thanks

Paul

VBA controlled conditional format
 

Change your formula to the form

=if(E1="","",Vlookup(E1,F1:G35,2,0))


for the above

activecell.Formula = "=IF(E1="""","""",Vlookup(E1,F1:G35,2,0))"

just to illustrate how you would enter "" inside your string.

--
Regards,
Tom Ogilvy



"Paul" <paulm dot c @ iol dot ie wrote in message
...
Hi,

I'm using a small script to copy a formula down to certain cells based on
an
option button selection by the user. For example (this is the code for one
of the option buttons)

Private Sub Opt20_Click()
Dim wks
Set wks = Worksheets("Interface")
wks.Range("f13:f1000").ClearContents
For x = 13 To 32
AccDesc = "=VLOOKUP($E$" & x & " ,[Data.xls]SAP_COA_Map!$D:$E,2)"
wks.Range("f" & x) = AccDesc
Next x
Set wks = Nothing
End Sub

When the user selects this button the formula is copied down and each cell
is calculated and a value is return. The problem here is that the user
will
select the button before anything is entered into cell range E:E with the
result that the cells displays "#N/A" because the formula doesn't have
anything to work with.

Using code how do I hide the result in the cell if it is #N/A

Thanks

Paul

VBA controlled conditional format
 

Thanks Tom, Thats fixed it

One more question you might be able to help with. After populating a number
of colums with formulae some fields may return an "#N/A" - in most cases
just simply because there is no data to return. I want to be able to capture
these so I can perform further action if an "#N/A" is found.

I could specify the range in question and loop through each cell then
perform the action if one is found but is there a better way to do this than
looping through each cell individually i.e. can I search the whole worksheet
at once and apply my code as soon as an N/A is found (The code I want to run
is the same no matter where the N/A is found)

Again thanks for your help

Paul

"Tom Ogilvy" wrote in message
...
Change your formula to the form

=if(E1="","",Vlookup(E1,F1:G35,2,0))


for the above

activecell.Formula = "=IF(E1="""","""",Vlookup(E1,F1:G35,2,0))"

just to illustrate how you would enter "" inside your string.

--
Regards,
Tom Ogilvy



"Paul" <paulm dot c @ iol dot ie wrote in message
...
Hi,

I'm using a small script to copy a formula down to certain cells based on
an
option button selection by the user. For example (this is the code for
one
of the option buttons)

Private Sub Opt20_Click()
Dim wks
Set wks = Worksheets("Interface")
wks.Range("f13:f1000").ClearContents
For x = 13 To 32
AccDesc = "=VLOOKUP($E$" & x & " ,[Data.xls]SAP_COA_Map!$D:$E,2)"
wks.Range("f" & x) = AccDesc
Next x
Set wks = Nothing
End Sub

When the user selects this button the formula is copied down and each
cell
is calculated and a value is return. The problem here is that the user
will
select the button before anything is entered into cell range E:E with the
result that the cells displays "#N/A" because the formula doesn't have
anything to work with.

Using code how do I hide the result in the cell if it is #N/A

Thanks

Paul

VBA controlled conditional format
 

Perhaps something like the below:

Dim rng as Range, cell as Range
On Error Resume Next
set rng = Cells.SpecialCells(xlFormulas,xlErrors)
On Error goto 0
for each cell in rng
if cell.Value = cvErr(xlNa) then


--
Regards,
Tom Ogilvy

"Paul" <paulm dot c @ iol dot ie wrote in message
...
Thanks Tom, Thats fixed it

One more question you might be able to help with. After populating a
number
of colums with formulae some fields may return an "#N/A" - in most cases
just simply because there is no data to return. I want to be able to
capture
these so I can perform further action if an "#N/A" is found.

I could specify the range in question and loop through each cell then
perform the action if one is found but is there a better way to do this
than
looping through each cell individually i.e. can I search the whole
worksheet
at once and apply my code as soon as an N/A is found (The code I want to
run
is the same no matter where the N/A is found)

Again thanks for your help

Paul

"Tom Ogilvy" wrote in message
...
Change your formula to the form

=if(E1="","",Vlookup(E1,F1:G35,2,0))


for the above

activecell.Formula = "=IF(E1="""","""",Vlookup(E1,F1:G35,2,0))"

just to illustrate how you would enter "" inside your string.

--
Regards,
Tom Ogilvy



"Paul" <paulm dot c @ iol dot ie wrote in message
...
Hi,

I'm using a small script to copy a formula down to certain cells based
on
an
option button selection by the user. For example (this is the code for
one
of the option buttons)

Private Sub Opt20_Click()
Dim wks
Set wks = Worksheets("Interface")
wks.Range("f13:f1000").ClearContents
For x = 13 To 32
AccDesc = "=VLOOKUP($E$" & x & " ,[Data.xls]SAP_COA_Map!$D:$E,2)"
wks.Range("f" & x) = AccDesc
Next x
Set wks = Nothing
End Sub

When the user selects this button the formula is copied down and each
cell
is calculated and a value is return. The problem here is that the user
will
select the button before anything is entered into cell range E:E with
the
result that the cells displays "#N/A" because the formula doesn't have
anything to work with.

Using code how do I hide the result in the cell if it is #N/A

Thanks

Paul