Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
BeenThereGotLost
 
Posts: n/a
Default Performing a function on a column of variable length

I am cutting and pasting rows of data from our ERP system into a spread
sheet. I then create a table at the end of the data calculating various
statistics. The column layout is fixed, so I always know which column I need
to perform the functions. The number of Row is NOT fixed, so I need a way to
indicate the end of the range.

Something like

=AVERAGE(RegionAround(B2,ColumnOnly))
where RegionAround(B2,ColumnOnly) would return B2:<row before first
blank cell

I'm willing to write a VBA function to return this information, but I can't
get the recieving function to accept the value back as a range.

I am open to all ideas of performing functions on variable length columns.

Thanks,

ER.
  #2   Report Post  
Niek Otten
 
Posts: n/a
Default

You could use this UDF:

Function EndCellTxt(a As Range) As String
EndCellTxt = a.End(xlDown).Address
End Function

Use with the INDIRECT function, like in

=SUM(B2:INDIRECT(endcelltxt(B2)))

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
"BeenThereGotLost" wrote in
message ...
I am cutting and pasting rows of data from our ERP system into a spread
sheet. I then create a table at the end of the data calculating various
statistics. The column layout is fixed, so I always know which column I
need
to perform the functions. The number of Row is NOT fixed, so I need a way
to
indicate the end of the range.

Something like

=AVERAGE(RegionAround(B2,ColumnOnly))
where RegionAround(B2,ColumnOnly) would return B2:<row before first
blank cell

I'm willing to write a VBA function to return this information, but I
can't
get the recieving function to accept the value back as a range.

I am open to all ideas of performing functions on variable length columns.

Thanks,

ER.



  #3   Report Post  
Niek Otten
 
Posts: n/a
Default

I think that in order to cope with lovalized versions and other reference
style the function should be

Function EndCellTxt(a As Range) As String
EndCellTxt = a.End(xlDown).AddressLocal(, , Application.ReferenceStyle)
End Function

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
"Niek Otten" wrote in message
...
You could use this UDF:

Function EndCellTxt(a As Range) As String
EndCellTxt = a.End(xlDown).Address
End Function

Use with the INDIRECT function, like in

=SUM(B2:INDIRECT(endcelltxt(B2)))

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
"BeenThereGotLost" wrote in
message ...
I am cutting and pasting rows of data from our ERP system into a spread
sheet. I then create a table at the end of the data calculating various
statistics. The column layout is fixed, so I always know which column I
need
to perform the functions. The number of Row is NOT fixed, so I need a way
to
indicate the end of the range.

Something like

=AVERAGE(RegionAround(B2,ColumnOnly))
where RegionAround(B2,ColumnOnly) would return B2:<row before first
blank cell

I'm willing to write a VBA function to return this information, but I
can't
get the recieving function to accept the value back as a range.

I am open to all ideas of performing functions on variable length
columns.

Thanks,

ER.





  #4   Report Post  
BeenThereGotLost
 
Posts: n/a
Default

Many Thanks, this is exactly what I needed. I did not realize you could use
the Indirect() function on the sides of the ":" in specifying the range.

ER.

"Niek Otten" wrote:

You could use this UDF:

Function EndCellTxt(a As Range) As String
EndCellTxt = a.End(xlDown).Address
End Function

Use with the INDIRECT function, like in

=SUM(B2:INDIRECT(endcelltxt(B2)))

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
"BeenThereGotLost" wrote in
message ...
I am cutting and pasting rows of data from our ERP system into a spread
sheet. I then create a table at the end of the data calculating various
statistics. The column layout is fixed, so I always know which column I
need
to perform the functions. The number of Row is NOT fixed, so I need a way
to
indicate the end of the range.

Something like

=AVERAGE(RegionAround(B2,ColumnOnly))
where RegionAround(B2,ColumnOnly) would return B2:<row before first
blank cell

I'm willing to write a VBA function to return this information, but I
can't
get the recieving function to accept the value back as a range.

I am open to all ideas of performing functions on variable length columns.

Thanks,

ER.




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
Combining workbooks with some variable field names Bob Dobalina Excel Discussion (Misc queries) 8 May 17th 05 09:48 PM
How to group similar column titles together???? vrk1 Excel Discussion (Misc queries) 2 April 30th 05 12:17 AM
compile error: expected variable or function MMM Excel Discussion (Misc queries) 3 December 24th 04 04:11 PM
How can I sort an entire spreadsheet from a list prod sorter Excel Worksheet Functions 4 November 17th 04 04:43 AM
change function variable prompts?? thinkingfield Excel Worksheet Functions 1 November 8th 04 05:01 PM


All times are GMT +1. The time now is 03:05 PM.

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

About Us

"It's about Microsoft Excel"