Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 18th 05, 08:14 PM
bryan
 
Posts: n/a
Default ClearContents method on a passed range

I want to create a range of results in one worksheet based on a range
of input values in another worksheet in the same workbook. I've
'named' the input and result ranges in Excel but am unable to clear
the result range using the following code called from Excel with
'=Test1(results)' speciified in a cell:

Public Function Test1(RangeResult As Range)

RangeResult.ClearContents

End Function

The code runs o.k. but the range isn't cleared and zero is returned. I
thought I was getting the hang of VBA but this has stumped me.

  #2   Report Post  
Old January 18th 05, 08:21 PM
Harlan Grove
 
Posts: n/a
Default

bryan wrote...
I want to create a range of results in one worksheet based on a range
of input values in another worksheet in the same workbook. I've
'named' the input and result ranges in Excel but am unable to clear
the result range using the following code called from Excel with
'=Test1(results)' speciified in a cell:

Public Function Test1(RangeResult As Range)

RangeResult.ClearContents

End Function

The code runs o.k. but the range isn't cleared and zero is returned. I
thought I was getting the hang of VBA but this has stumped me.


This isn't well documented, but VBA procedures called from worksheet
formulas can't change anything in the Excel environment.

When do you want to clear the results range? Why do you need to clear
the results range rather than write formulas in it which could display
nothing (so appear cleared) when there's no corresponding inputs?

  #3   Report Post  
Old January 19th 05, 08:49 AM
 
Posts: n/a
Default

Harlan Grove wrote:

This isn't well documented, but VBA procedures called from worksheet
formulas can't change anything in the Excel environment.

When do you want to clear the results range? Why do you need to clear
the results range rather than write formulas in it which could

display
nothing (so appear cleared) when there's no corresponding inputs?


Thanks for replying.

The result range is a subset of the input range selected randomly. In
randomly selecting from the input rage, I wanted to avoid duplicates by
scanning the entries already selected. To make sure that the result
range is initially empty, I wanted to clear it.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing a range name as an argument to the Index Function Michael Sharpe Excel Discussion (Misc queries) 3 September 5th 12 01:33 PM
Defined range difficulty Pat Excel Discussion (Misc queries) 7 January 16th 05 09:52 PM
Construct a range in VB Steve Excel Discussion (Misc queries) 3 December 29th 04 02:01 PM
named range refers to: in a chart Spencer Hutton Excel Discussion (Misc queries) 1 December 14th 04 10:15 PM
HTML_Control Range name Steven Cheng Excel Discussion (Misc queries) 4 December 10th 04 10:12 PM


All times are GMT +1. The time now is 07:40 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017