Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveCell.End(xlDown).Select not working consistently
I have some vba set up to import a query from access, go to the top of
a column, and select to the last value. When I run the two Subroutines seperately, they work fine. However when I call them from another Sub, the whole column from the top down to the bottom of the sheet is selected, ruining the next part? Does anyone have any clues as to why this is happening? Thanks. ################################################## ############################# Sub ImportData() ' ' Macro to import summary data according to LID from MSAccess, qryBudgetMonitoringDetailSubtotals ' Dim strBudgetManager As String On Error Resume Next ' Verify a LID has been set, if not get one If Range("LID").Value = "" Then strLID = InputBox("What is your LID?") Range("LID") = strLID Else MsgBox "Your LID is " & Range("LID") End If ' Select then delete the "Summary" sheet to ensure clean data import Sheets("Summary").Select Application.DisplayAlerts = False ' Turns off delete sheet warning ActiveWindow.SelectedSheets.Delete Set wsNewWorkSheet = Worksheets.Add(after:=Worksheets(1)) wsNewWorkSheet.Name = "Summary" ' Set LID variable from home page, or by an input box strLID = Range("LID") MsgBox "Importing Data from Budget Monitoring" 'Import data from access With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=MS Access Database;DBQ=S:\REVMON\Budget Monitoring\Maintenance\current.mdb;DefaultDir=S:\R EVMON\Budget Monitoring\Maintenance;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTi" _ ), Array("meout=5;")), Destination:=Range("B4")) .CommandText = Array( _ "SELECT qryBudgetMonitoringDetailSubtotals.AllocationBM_LI D, qryBudgetMonitoringDetailSubtotals.`Budget Manager`, qryBudgetMonitoringDetailSubtotals.AllocationFMO_L ID, qryBudgetMonitoringDetailSubtotal" _ , _ "s.FMO, qryBudgetMonitoringDetailSubtotals.CostCentre, qryBudgetMonitoringDetailSubtotals.CostCentreDescr iption, qryBudgetMonitoringDetailSubtotals.Budget, qryBudgetMonitoringDetailSubtotals.Spend, qry" _ , _ "BudgetMonitoringDetailSubtotals.Projected, qryBudgetMonitoringDetailSubtotals.Variance" & Chr(13) & "" & Chr(10) & "FROM `S:\REVMON\Budget Monitoring\Maintenance\current.mdb`.qryBudgetMonit oringDetailSubtotals qryBudgetMonitoringDetailSubtotals" & Chr(13) & "" & Chr(10) & "WHERE (qryBud" _ , _ "getMonitoringDetailSubtotals.AllocationBM_LID ='" & strLID & "')" & Chr(13) & "" & Chr(10) & "ORDER BY qryBudgetMonitoringDetailSubtotals.CostCentre" _ ) .Name = "BudgetSubtotalsImport" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery = False End With End Sub ################################################## ############################# Sub RangeNaming() Sheets("Summary").Select ' Set CostCenters range Range("F4").Select Range(Selection, Selection.End(xlDown)).Select If Selection.Count 2 Then Range("F5").Select Range(ActiveCell, ActiveCell.End(xlDown)).Select Selection.Name = "CostCenters" Else Range("F5").Select Selection.Name = "CostCenters" End If ' Set CostCenterDescriptions Range("G4").Select Range(ActiveCell, ActiveCell.End(xlDown)).Select If Selection.Count < 2 Then Range("G5").Select Range(ActiveCell, ActiveCell.End(xlDown)).Select Selection.Name = "CCDescriptions" Else Range("G5").Select Selection.Name = "CCDescriptions" End If ' Set Budget Managers Name strBudgetManager = Range("C4").Value MsgBox strBudgetManager Range("F1").Value = "Budget Monitoring Summary for " & strBudgetManager Range("F1").Select With Selection .Font.Size = 18 .Font.Bold = True End With ' Set Monitor Details Range("F2").Value = strCurrentMonitor & ", " & strCurrentYear Range("F2:K2").Select With Selection .HorizontalAlignment = xlCenter .MergeCells = True .Font.Size = 14 .Font.Bold = True End With ' Set FinancialMonitoringOfficer Range("E4").Select Range(ActiveCell, ActiveCell.End(xlDown)).Select If Selection.Count < 2 Then Range("E5").Select Range(ActiveCell, ActiveCell.End(xlDown)).Select Selection.Name = "FinancialMonitoringOfficer" Else Range("E5").Select Selection.Name = "FinancialMonitoringOfficer" End If ' Hide uneeded colums Columns("B:E").Select ' BM LID Column Selection.EntireColumn.Hidden = True ' Tidy up Range("F4").Select Application.DisplayAlerts = True ' Sets notifications back on. This should be used for any changed Excel settings End Sub ################################################## ############################# Sub RefreshAllData() ' ' Macro which updates all data (Imported summary and relevant sheets), and provides a stop check ' Ans = MsgBox("You are about to update all the data in your Budget Monitor. Choose OK to continue or Cancel to quit", vbOKCancel, "Last chance to quit!") If Ans = vbOK Then Application.Run "ImportData" Application.Run "RangeNaming" Application.Run "AddSheet" Application.Run "AddDesc" Application.Run "AddFMO" Else End End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveCell.End(xlDown).Select not working consistently
Instead of using Range(Selection,Selection.end(xldown))
use Range(Selection,Cells(rows.count,ActiveCell.column ).End(xlup)) -- Regards, Tom Ogilvy "reclusive monkey" wrote in message om... I have some vba set up to import a query from access, go to the top of a column, and select to the last value. When I run the two Subroutines seperately, they work fine. However when I call them from another Sub, the whole column from the top down to the bottom of the sheet is selected, ruining the next part? Does anyone have any clues as to why this is happening? Thanks. ################################################## ########################## ### Sub ImportData() ' ' Macro to import summary data according to LID from MSAccess, qryBudgetMonitoringDetailSubtotals ' Dim strBudgetManager As String On Error Resume Next ' Verify a LID has been set, if not get one If Range("LID").Value = "" Then strLID = InputBox("What is your LID?") Range("LID") = strLID Else MsgBox "Your LID is " & Range("LID") End If ' Select then delete the "Summary" sheet to ensure clean data import Sheets("Summary").Select Application.DisplayAlerts = False ' Turns off delete sheet warning ActiveWindow.SelectedSheets.Delete Set wsNewWorkSheet = Worksheets.Add(after:=Worksheets(1)) wsNewWorkSheet.Name = "Summary" ' Set LID variable from home page, or by an input box strLID = Range("LID") MsgBox "Importing Data from Budget Monitoring" 'Import data from access With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=MS Access Database;DBQ=S:\REVMON\Budget Monitoring\Maintenance\current.mdb;DefaultDir=S:\R EVMON\Budget Monitoring\Maintenance;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTi" _ ), Array("meout=5;")), Destination:=Range("B4")) .CommandText = Array( _ "SELECT qryBudgetMonitoringDetailSubtotals.AllocationBM_LI D, qryBudgetMonitoringDetailSubtotals.`Budget Manager`, qryBudgetMonitoringDetailSubtotals.AllocationFMO_L ID, qryBudgetMonitoringDetailSubtotal" _ , _ "s.FMO, qryBudgetMonitoringDetailSubtotals.CostCentre, qryBudgetMonitoringDetailSubtotals.CostCentreDescr iption, qryBudgetMonitoringDetailSubtotals.Budget, qryBudgetMonitoringDetailSubtotals.Spend, qry" _ , _ "BudgetMonitoringDetailSubtotals.Projected, qryBudgetMonitoringDetailSubtotals.Variance" & Chr(13) & "" & Chr(10) & "FROM `S:\REVMON\Budget Monitoring\Maintenance\current.mdb`.qryBudgetMonit oringDetailSubtotals qryBudgetMonitoringDetailSubtotals" & Chr(13) & "" & Chr(10) & "WHERE (qryBud" _ , _ "getMonitoringDetailSubtotals.AllocationBM_LID ='" & strLID & "')" & Chr(13) & "" & Chr(10) & "ORDER BY qryBudgetMonitoringDetailSubtotals.CostCentre" _ ) .Name = "BudgetSubtotalsImport" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery = False End With End Sub ################################################## ########################## ### Sub RangeNaming() Sheets("Summary").Select ' Set CostCenters range Range("F4").Select Range(Selection, Selection.End(xlDown)).Select If Selection.Count 2 Then Range("F5").Select Range(ActiveCell, ActiveCell.End(xlDown)).Select Selection.Name = "CostCenters" Else Range("F5").Select Selection.Name = "CostCenters" End If ' Set CostCenterDescriptions Range("G4").Select Range(ActiveCell, ActiveCell.End(xlDown)).Select If Selection.Count < 2 Then Range("G5").Select Range(ActiveCell, ActiveCell.End(xlDown)).Select Selection.Name = "CCDescriptions" Else Range("G5").Select Selection.Name = "CCDescriptions" End If ' Set Budget Managers Name strBudgetManager = Range("C4").Value MsgBox strBudgetManager Range("F1").Value = "Budget Monitoring Summary for " & strBudgetManager Range("F1").Select With Selection .Font.Size = 18 .Font.Bold = True End With ' Set Monitor Details Range("F2").Value = strCurrentMonitor & ", " & strCurrentYear Range("F2:K2").Select With Selection .HorizontalAlignment = xlCenter .MergeCells = True .Font.Size = 14 .Font.Bold = True End With ' Set FinancialMonitoringOfficer Range("E4").Select Range(ActiveCell, ActiveCell.End(xlDown)).Select If Selection.Count < 2 Then Range("E5").Select Range(ActiveCell, ActiveCell.End(xlDown)).Select Selection.Name = "FinancialMonitoringOfficer" Else Range("E5").Select Selection.Name = "FinancialMonitoringOfficer" End If ' Hide uneeded colums Columns("B:E").Select ' BM LID Column Selection.EntireColumn.Hidden = True ' Tidy up Range("F4").Select Application.DisplayAlerts = True ' Sets notifications back on. This should be used for any changed Excel settings End Sub ################################################## ########################## ### Sub RefreshAllData() ' ' Macro which updates all data (Imported summary and relevant sheets), and provides a stop check ' Ans = MsgBox("You are about to update all the data in your Budget Monitor. Choose OK to continue or Cancel to quit", vbOKCancel, "Last chance to quit!") If Ans = vbOK Then Application.Run "ImportData" Application.Run "RangeNaming" Application.Run "AddSheet" Application.Run "AddDesc" Application.Run "AddFMO" Else End End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveCell.End(xlDown).Select not working consistently
"Tom Ogilvy" wrote in message ...
Instead of using Range(Selection,Selection.end(xldown)) use Range(Selection,Cells(rows.count,ActiveCell.column ).End(xlup)) -- Regards, Tom Ogilvy Thanks for the tip Tom, but unfortunately that doesn't work either, it selects a range above what I want across several columns. I think I will just run the macros myself for an easy life! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use xldown to select a range of cells | Excel Discussion (Misc queries) | |||
Vlookup not working consistently | Excel Worksheet Functions | |||
End(xlDown) not working? | Excel Discussion (Misc queries) | |||
Hyperlink to .WAV file not working consistently | Links and Linking in Excel | |||
Conditional Formatting Not Working Consistently | Excel Discussion (Misc queries) |