View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson Dave Peterson is offline
external usenet poster
 
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