Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening at specific cell - No Macro | Excel Discussion (Misc queries) | |||
pick up last occupied cell to the left macro | New Users to Excel | |||
Pick up file from specific location | Excel Discussion (Misc queries) | |||
How I can pick a specific value on an array in excel | Excel Discussion (Misc queries) | |||
How do I run a Macro on basic of a value in a specific cell | Excel Worksheet Functions |