Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Go to cell with maximum value when worksheet opened
I have a workbook containing a number of worksheets, some of which contain
weekly data extracted from a database and which are regularly updated, and others perform various calculations on the data to produce reports and graphs. After updating the data I want to be able to visually check the latest week of data entry in one of the worksheets, and am trying to write a macro that will place the cursor on the first cell of the row containing the most recent date (i.e. maximum date value) when the sheet is opened rather than having to scroll down through an increasingly long column each time. I have tried several things without success - I am new to macro writing - can anyone help with a suggestion please? Thanks in anticipation. -- pignick |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Go to cell with maximum value when worksheet opened
Hi,
This select the last used cell in Column A. Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Select Mike "pignick" wrote: I have a workbook containing a number of worksheets, some of which contain weekly data extracted from a database and which are regularly updated, and others perform various calculations on the data to produce reports and graphs. After updating the data I want to be able to visually check the latest week of data entry in one of the worksheets, and am trying to write a macro that will place the cursor on the first cell of the row containing the most recent date (i.e. maximum date value) when the sheet is opened rather than having to scroll down through an increasingly long column each time. I have tried several things without success - I am new to macro writing - can anyone help with a suggestion please? Thanks in anticipation. -- pignick |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Go to cell with maximum value when worksheet opened
Assuming dates are contiguous in column A and latest date will be last entry
Private Sub Worksheet_Activate() Me.Cells(Rows.Count, 1).End(xlUp).Select End Sub Right-click on the sheet tab and "View Code". Copy/paste the above into that sheet module. Edit the "1" to your column number. Alt + q to return to Excel window. Gord Dibben MS Excel MVP On Sun, 13 Jul 2008 08:56:01 -0700, pignick wrote: I have a workbook containing a number of worksheets, some of which contain weekly data extracted from a database and which are regularly updated, and others perform various calculations on the data to produce reports and graphs. After updating the data I want to be able to visually check the latest week of data entry in one of the worksheets, and am trying to write a macro that will place the cursor on the first cell of the row containing the most recent date (i.e. maximum date value) when the sheet is opened rather than having to scroll down through an increasingly long column each time. I have tried several things without success - I am new to macro writing - can anyone help with a suggestion please? Thanks in anticipation. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Go to cell with maximum value when worksheet opened
Thanks to both Mike & Gord for your suggestions, but that has only got me
part way there. I can now see how to write code that is implemented when a particular worksheet is opened (Gord's answer), but unfortunately the result using either of your code lines is taking me to the last cell that contains a look-up formula linked to the database extraction, which is well below where the last actual date is. i.e. I need to get to the cell with a maximum value - have tried writing various IF statements to make the cell values zero or " ", but it seems to make no difference and always goes to the last cell in the column containing a formula. I have tried various ways of using MAX but keep getting syntax errors, as it's apparently not as simple as using MAX(A1:A400) in a cell formula, or am I missing something simple here? Can anyone offer any further help on this please? -- pignick "Gord Dibben" wrote: Assuming dates are contiguous in column A and latest date will be last entry Private Sub Worksheet_Activate() Me.Cells(Rows.Count, 1).End(xlUp).Select End Sub Right-click on the sheet tab and "View Code". Copy/paste the above into that sheet module. Edit the "1" to your column number. Alt + q to return to Excel window. Gord Dibben MS Excel MVP On Sun, 13 Jul 2008 08:56:01 -0700, pignick wrote: I have a workbook containing a number of worksheets, some of which contain weekly data extracted from a database and which are regularly updated, and others perform various calculations on the data to produce reports and graphs. After updating the data I want to be able to visually check the latest week of data entry in one of the worksheets, and am trying to write a macro that will place the cursor on the first cell of the row containing the most recent date (i.e. maximum date value) when the sheet is opened rather than having to scroll down through an increasingly long column each time. I have tried several things without success - I am new to macro writing - can anyone help with a suggestion please? Thanks in anticipation. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Go to cell with maximum value when worksheet opened
In which column will your dates be entered?
Will that column contain dates only or? Gord On Sun, 13 Jul 2008 11:24:00 -0700, pignick wrote: Thanks to both Mike & Gord for your suggestions, but that has only got me part way there. I can now see how to write code that is implemented when a particular worksheet is opened (Gord's answer), but unfortunately the result using either of your code lines is taking me to the last cell that contains a look-up formula linked to the database extraction, which is well below where the last actual date is. i.e. I need to get to the cell with a maximum value - have tried writing various IF statements to make the cell values zero or " ", but it seems to make no difference and always goes to the last cell in the column containing a formula. I have tried various ways of using MAX but keep getting syntax errors, as it's apparently not as simple as using MAX(A1:A400) in a cell formula, or am I missing something simple here? Can anyone offer any further help on this please? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Go to cell with maximum value when worksheet opened
Hi Gord,
Dates are in column A in the range A7:A400 (rows above contain some headers, etc.). I haven't tried it yet as been very busy on other things today, but wondered if perhaps copying that whole range and then doing a paste values would then allow your original suggestion to work and find the maximum value (i.e. last date). Does that make sense or do you have any other suggestions. Is there an equivalent of COUNTIF in VBA? Thanks in anticipation of further help to crack this one. -- pignick "Gord Dibben" wrote: In which column will your dates be entered? Will that column contain dates only or? Gord On Sun, 13 Jul 2008 11:24:00 -0700, pignick wrote: Thanks to both Mike & Gord for your suggestions, but that has only got me part way there. I can now see how to write code that is implemented when a particular worksheet is opened (Gord's answer), but unfortunately the result using either of your code lines is taking me to the last cell that contains a look-up formula linked to the database extraction, which is well below where the last actual date is. i.e. I need to get to the cell with a maximum value - have tried writing various IF statements to make the cell values zero or " ", but it seems to make no difference and always goes to the last cell in the column containing a formula. I have tried various ways of using MAX but keep getting syntax errors, as it's apparently not as simple as using MAX(A1:A400) in a cell formula, or am I missing something simple here? Can anyone offer any further help on this please? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Go to cell with maximum value when worksheet opened
Got around to taking another look at this today and I've cracked it! I
placed a COUNTIF formula (value 0) in a cell outside the range and then used this value in an OFFSET to select the cell with the last date when the sheet is opened. Hope this might be of help to others. -- pignick "pignick" wrote: I have a workbook containing a number of worksheets, some of which contain weekly data extracted from a database and which are regularly updated, and others perform various calculations on the data to produce reports and graphs. After updating the data I want to be able to visually check the latest week of data entry in one of the worksheets, and am trying to write a macro that will place the cursor on the first cell of the row containing the most recent date (i.e. maximum date value) when the sheet is opened rather than having to scroll down through an increasingly long column each time. I have tried several things without success - I am new to macro writing - can anyone help with a suggestion please? Thanks in anticipation. -- pignick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to get a cell to only update when worksheet is opened? | Excel Discussion (Misc queries) | |||
Formating Cells used code when worksheet opened | Excel Worksheet Functions | |||
Excel not displaying the last active worksheet when opened | Excel Discussion (Misc queries) | |||
Can custom views be opened in a worksheet by other users? | Excel Discussion (Misc queries) | |||
How do I protect a worksheet from being opened inside a workbook | Excel Discussion (Misc queries) |