Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 25th 10, 06:54 AM posted to microsoft.public.excel.misc
Edi Edi is offline
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2007
Posts: 9
Default How to pick a specific cell within a macro .... ??

I receive spreadsheets with data from a number of different sources, in
different formats, but with essentially the same type of information. I need
to name about 5 ranges for my macro, but each range begins in a different
cell reference i.e. one range from one source may start at A4, whereas the
other source may have the same data beginning at B2.

I would like to tell my analysis spreadsheet which specific cell to start in
to name the data range.

Is there anyway of doing this?

What would be ideal would be some little message saying "Select the cell
containing the first row of DATE data", and then being able to click in the
first DATE cell, but I'm not sure that this is possible!!

I hope you can understand what I'm after, but if not, let me know and I'll
try to clarify it!

Thanking you in advance!

  #2   Report Post  
Old March 25th 10, 07:04 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8,521
Default How to pick a specific cell within a macro .... ??

Check out whether this will help...

Dim rngTemp As Range
Set rngTemp = Range(Split(ActiveSheet.UsedRange.Address, ":")(0))
MsgBox rngTemp.Address

--
Jacob


"Edi" wrote:

I receive spreadsheets with data from a number of different sources, in
different formats, but with essentially the same type of information. I need
to name about 5 ranges for my macro, but each range begins in a different
cell reference i.e. one range from one source may start at A4, whereas the
other source may have the same data beginning at B2.

I would like to tell my analysis spreadsheet which specific cell to start in
to name the data range.

Is there anyway of doing this?

What would be ideal would be some little message saying "Select the cell
containing the first row of DATE data", and then being able to click in the
first DATE cell, but I'm not sure that this is possible!!

I hope you can understand what I'm after, but if not, let me know and I'll
try to clarify it!

Thanking you in advance!

  #3   Report Post  
Old March 25th 10, 09:42 AM posted to microsoft.public.excel.misc
Edi Edi is offline
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2007
Posts: 9
Default How to pick a specific cell within a macro .... ??

Hi Jacob

Thanks for taking the time to help!

I copy the worksheet when it is forwarded to me, then want to go to my
template and simply click a button that basically says "Go" or Start".

it pastes into a new sheet, and then once all the data is in the new sheet,
i need to actually tell it where the first row of each column of data is ...
so i almost need the macro to pause while i tell it where the first row is
for each of the 5-6 columns of data, which are ranges that all need to be
named. i'm happy about naming the ranges etc, it's just identifying the
first row of data that i'm a bit stumped with, which as i mentioned changes
depending on where the raw data comes from!!


thanks again! mike



"Jacob Skaria" wrote:

Check out whether this will help...

Dim rngTemp As Range
Set rngTemp = Range(Split(ActiveSheet.UsedRange.Address, ":")(0))
MsgBox rngTemp.Address

--
Jacob


"Edi" wrote:

I receive spreadsheets with data from a number of different sources, in
different formats, but with essentially the same type of information. I need
to name about 5 ranges for my macro, but each range begins in a different
cell reference i.e. one range from one source may start at A4, whereas the
other source may have the same data beginning at B2.

I would like to tell my analysis spreadsheet which specific cell to start in
to name the data range.

Is there anyway of doing this?

What would be ideal would be some little message saying "Select the cell
containing the first row of DATE data", and then being able to click in the
first DATE cell, but I'm not sure that this is possible!!

I hope you can understand what I'm after, but if not, let me know and I'll
try to clarify it!

Thanking you in advance!

  #4   Report Post  
Old March 25th 10, 12:23 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default How to pick a specific cell within a macro .... ??

If you're using different workbooks, you'll have to change windows to point to
the cells in the different worksheets in those different workbooks.

Dim FirstRowCell as Range
Dim FirstDateCell as range

set firstrowcell = nothing
on error resume next
set firstrowcell = application.inputbox(Prompt:="Select first row of data", _
type:=8).cells(1)
on error goto 0

if firstrowcell is nothing then
'user hit cancel
'exit sub
end if

'second input

set firstdatecell = nothing
on error resume next
set firstdatecell = application.inputbox(Prompt:="Select first date cell", _
type:=8).cells(1)
on error goto 0

if firstdatecell is nothing then
'user hit cancel
'exit sub
end if

Then you can use something like:

'to get the row number or column
firstdatecell.row
firstdatecell.column
or
'to worksheet that holds that cell
firstdatecell.parent
or
'to get the workbook that holds the worksheet that holds the cell
firstdatecell.parent.parent



Edi wrote:

I receive spreadsheets with data from a number of different sources, in
different formats, but with essentially the same type of information. I need
to name about 5 ranges for my macro, but each range begins in a different
cell reference i.e. one range from one source may start at A4, whereas the
other source may have the same data beginning at B2.

I would like to tell my analysis spreadsheet which specific cell to start in
to name the data range.

Is there anyway of doing this?

What would be ideal would be some little message saying "Select the cell
containing the first row of DATE data", and then being able to click in the
first DATE cell, but I'm not sure that this is possible!!

I hope you can understand what I'm after, but if not, let me know and I'll
try to clarify it!

Thanking you in advance!


--

Dave Peterson
  #5   Report Post  
Old March 26th 10, 10:49 AM posted to microsoft.public.excel.misc
Edi Edi is offline
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2007
Posts: 9
Default How to pick a specific cell within a macro .... ??

thanks dave

the application.input type8 is exactly what i'm after! cheers!

i never realised that you could select a cell outside the prompt that comes
up! brilliant!!



mike

"Dave Peterson" wrote:

If you're using different workbooks, you'll have to change windows to point to
the cells in the different worksheets in those different workbooks.

Dim FirstRowCell as Range
Dim FirstDateCell as range

set firstrowcell = nothing
on error resume next
set firstrowcell = application.inputbox(Prompt:="Select first row of data", _
type:=8).cells(1)
on error goto 0

if firstrowcell is nothing then
'user hit cancel
'exit sub
end if

'second input

set firstdatecell = nothing
on error resume next
set firstdatecell = application.inputbox(Prompt:="Select first date cell", _
type:=8).cells(1)
on error goto 0

if firstdatecell is nothing then
'user hit cancel
'exit sub
end if

Then you can use something like:

'to get the row number or column
firstdatecell.row
firstdatecell.column
or
'to worksheet that holds that cell
firstdatecell.parent
or
'to get the workbook that holds the worksheet that holds the cell
firstdatecell.parent.parent



Edi wrote:

I receive spreadsheets with data from a number of different sources, in
different formats, but with essentially the same type of information. I need
to name about 5 ranges for my macro, but each range begins in a different
cell reference i.e. one range from one source may start at A4, whereas the
other source may have the same data beginning at B2.

I would like to tell my analysis spreadsheet which specific cell to start in
to name the data range.

Is there anyway of doing this?

What would be ideal would be some little message saying "Select the cell
containing the first row of DATE data", and then being able to click in the
first DATE cell, but I'm not sure that this is possible!!

I hope you can understand what I'm after, but if not, let me know and I'll
try to clarify it!

Thanking you in advance!


--

Dave Peterson
.



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
Opening at specific cell - No Macro TexJen09 Excel Discussion (Misc queries) 0 November 21st 08 05:49 PM
pick up last occupied cell to the left macro Meanie New Users to Excel 5 June 24th 08 01:24 PM
Pick up file from specific location Mandeep Dhami Excel Discussion (Misc queries) 2 January 10th 08 10:32 AM
How I can pick a specific value on an array in excel Paul Excel Discussion (Misc queries) 1 May 10th 05 07:38 PM
How do I run a Macro on basic of a value in a specific cell Steen H. Pedersen Excel Worksheet Functions 3 April 16th 05 08:52 AM


All times are GMT +1. The time now is 07:59 AM.

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