Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 3rd 03, 07:53 PM posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2003
Posts: 2
Default I need to determine a cell range based on a blank cell

Debra Dalgleish wrote in message ...
You could use a dynamic formula to define the range in Excel. It will
automatically adjust to include only the filled rows. There are some
instructions he

http://www.contextures.com/xlNames01.html#Dynamic


That's not working for me. I'm getting a #VALUE! error.

I realized I should give a little more information. I'm using Windows
2000, Excel XP and the VB program is created with VB 6.

Here is what my table will look like:

Phone number Reason Code Volume
800-555-2222 Abandon Call 6
Misc. Text 10
More Misc. Text 101
800-555-1111 Abandon Call 305
Text Text Text 3
More Text 4

For each report VB will dump the data into my Spreadsheet which may
consist of 3 rows or 30 rows. When VB copies and pastes this table
from excel I want it to know where the table should stop. VB does
know to look for the Name "TableRange1" which is usually an absolute
value (for example, A3:C24)but in this case I need to create it
dynamically with each report.

I hope that makes things clearer. I would appreciate any help anybody
can give me.

Thanks,
Katrina Haggerty

  #2   Report Post  
Old July 3rd 03, 09:11 PM posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2003
Posts: 52
Default I need to determine a cell range based on a blank cell

Using your example, the formula to define TableRange1 would be:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$B:$B),3)

This will create a range with rows equal to the number of items in
column B, and 3 columns.

Katrina wrote:
Debra Dalgleish wrote in message ...

You could use a dynamic formula to define the range in Excel. It will
automatically adjust to include only the filled rows. There are some
instructions he

http://www.contextures.com/xlNames01.html#Dynamic



That's not working for me. I'm getting a #VALUE! error.

I realized I should give a little more information. I'm using Windows
2000, Excel XP and the VB program is created with VB 6.

Here is what my table will look like:

Phone number Reason Code Volume
800-555-2222 Abandon Call 6
Misc. Text 10
More Misc. Text 101
800-555-1111 Abandon Call 305
Text Text Text 3
More Text 4

For each report VB will dump the data into my Spreadsheet which may
consist of 3 rows or 30 rows. When VB copies and pastes this table
from excel I want it to know where the table should stop. VB does
know to look for the Name "TableRange1" which is usually an absolute
value (for example, A3:C24)but in this case I need to create it
dynamically with each report.

I hope that makes things clearer. I would appreciate any help anybody
can give me.

Thanks,
Katrina Haggerty



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html



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
use active cell to determine range Gizmo Excel Discussion (Misc queries) 2 March 31st 08 04:55 AM
How can I make a blank cell in a formula cell with a range of cell Vi Excel Discussion (Misc queries) 5 June 21st 07 02:46 PM
Determine last blank cell in row? [email protected] Excel Worksheet Functions 8 May 21st 07 10:03 PM
Determine next Saturday in calendar based on date in a cell ann New Users to Excel 4 October 9th 06 05:09 PM
Function to determine if any cell in a range is contained in a given cell [email protected] Excel Worksheet Functions 3 February 7th 05 04:19 PM


All times are GMT +1. The time now is 04:32 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017