Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't select range
The following code works until the last line:
Option Explicit Public Sub TribalInvCheck() Dim wbTribalHist As Workbook Dim wbTribalTR As Workbook Dim wsTribalTR As Worksheet Dim wsTribalHist As Worksheet Dim rTRCell As Range Dim lTRRow As Long Dim lHistRow As Long Dim rFoundID As Range Dim sTRID As String Dim rTribalHist As Range Dim lHistCol As Long Set wbTribalHist = ThisWorkbook Set wbTribalTR = ActiveWorkbook Set wsTribalTR = ActiveSheet Set wsTribalHist = wbTribalHist.Worksheets("Historical") 'Range set temporarily until I can find out how to set the range to the longest column in the range Set rTribalHist = wsTribalHist.Range("A3:Iv150") 'Application.ScreenUpdating = False If ThisWorkbook.Name = ActiveWorkbook.Name Then MsgBox "Please do not run this macro from the workbook that contains it." _ & Chr(10) & "Please select a Turnaround Report and then restart this macro." Exit Sub End If Set rTRCell = wsTribalTR.Range("A3") sTRID = rTRCell.Value Set rFoundID = rTribalHist.Find(sTRID, LookIn:=xlValues) 'wsTribalHist.Activate 'rFoundID.Select lTRRow = 3 lHistRow = rFoundID.Row + 2 lHistCol = rFoundID.Column wsTribalHist.Activate wsTribalHist.Range(Cells(lHistRow, lHistCol)).Select <--- Method range of object worksheet failed End Sub Any ideas why it doesn't recognize this range? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't select range
I haven't tested it all the way through but I think you may need to change this line: wsTribalHist.Range(Cells(lHistRow, lHistCol)).Select to this: wsTribalHist.ActiveSheet.Cells(lHistRow, lHistCol).Select Hope this helps. "salgud" wrote: The following code works until the last line: Option Explicit Public Sub TribalInvCheck() Dim wbTribalHist As Workbook Dim wbTribalTR As Workbook Dim wsTribalTR As Worksheet Dim wsTribalHist As Worksheet Dim rTRCell As Range Dim lTRRow As Long Dim lHistRow As Long Dim rFoundID As Range Dim sTRID As String Dim rTribalHist As Range Dim lHistCol As Long Set wbTribalHist = ThisWorkbook Set wbTribalTR = ActiveWorkbook Set wsTribalTR = ActiveSheet Set wsTribalHist = wbTribalHist.Worksheets("Historical") 'Range set temporarily until I can find out how to set the range to the longest column in the range Set rTribalHist = wsTribalHist.Range("A3:Iv150") 'Application.ScreenUpdating = False If ThisWorkbook.Name = ActiveWorkbook.Name Then MsgBox "Please do not run this macro from the workbook that contains it." _ & Chr(10) & "Please select a Turnaround Report and then restart this macro." Exit Sub End If Set rTRCell = wsTribalTR.Range("A3") sTRID = rTRCell.Value Set rFoundID = rTribalHist.Find(sTRID, LookIn:=xlValues) 'wsTribalHist.Activate 'rFoundID.Select lTRRow = 3 lHistRow = rFoundID.Row + 2 lHistCol = rFoundID.Column wsTribalHist.Activate wsTribalHist.Range(Cells(lHistRow, lHistCol)).Select <--- Method range of object worksheet failed End Sub Any ideas why it doesn't recognize this range? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't select range
On Jul 26, 12:05*am, salgud wrote:
The following code works until the last line: Option Explicit Public Sub TribalInvCheck() Dim wbTribalHist As Workbook Dim wbTribalTR As Workbook Dim wsTribalTR As Worksheet Dim wsTribalHist As Worksheet Dim rTRCell As Range Dim lTRRow As Long Dim lHistRow As Long Dim rFoundID As Range Dim sTRID As String Dim rTribalHist As Range Dim lHistCol As Long Set wbTribalHist = ThisWorkbook Set wbTribalTR = ActiveWorkbook Set wsTribalTR = ActiveSheet Set wsTribalHist = wbTribalHist.Worksheets("Historical") 'Range set temporarily until I can find out how to set the range to the longest column in the range Set rTribalHist = wsTribalHist.Range("A3:Iv150") 'Application.ScreenUpdating = False If ThisWorkbook.Name = ActiveWorkbook.Name Then * MsgBox "Please do not run this macro from the workbook that contains it." _ * * & Chr(10) & "Please select a Turnaround Report and then restart this macro." * Exit Sub End If Set rTRCell = wsTribalTR.Range("A3") sTRID = rTRCell.Value Set rFoundID = rTribalHist.Find(sTRID, LookIn:=xlValues) 'wsTribalHist.Activate 'rFoundID.Select lTRRow = 3 lHistRow = rFoundID.Row + 2 lHistCol = rFoundID.Column wsTribalHist.Activate wsTribalHist.Range(Cells(lHistRow, lHistCol)).Select <--- Method range of object worksheet failed End Sub Any ideas why it doesn't recognize this range? Thanks! When you stop the code (i.e. put a breakpoint in) what are the variables lHistRow and lHistCol, to me the only thing that would make any sense is if it wasn't finding sTRID. Either that or the active workbook wasn't wbTribalHist, aslong as the activeworkbook is wbTribalHist (so try changing the wsTribalHist to wbTribalHist) the range should then select (I think). James |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't select range
On Jul 26, 12:23*am, XP wrote:
I haven't tested it all the way through but I think you may need to change this line: wsTribalHist.Range(Cells(lHistRow, lHistCol)).Select to this: wsTribalHist.ActiveSheet.Cells(lHistRow, lHistCol).Select Hope this helps. "salgud" wrote: The following code works until the last line: Option Explicit Public Sub TribalInvCheck() Dim wbTribalHist As Workbook Dim wbTribalTR As Workbook Dim wsTribalTR As Worksheet Dim wsTribalHist As Worksheet Dim rTRCell As Range Dim lTRRow As Long Dim lHistRow As Long Dim rFoundID As Range Dim sTRID As String Dim rTribalHist As Range Dim lHistCol As Long Set wbTribalHist = ThisWorkbook Set wbTribalTR = ActiveWorkbook Set wsTribalTR = ActiveSheet Set wsTribalHist = wbTribalHist.Worksheets("Historical") 'Range set temporarily until I can find out how to set the range to the longest column in the range Set rTribalHist = wsTribalHist.Range("A3:Iv150") 'Application.ScreenUpdating = False If ThisWorkbook.Name = ActiveWorkbook.Name Then * MsgBox "Please do not run this macro from the workbook that contains it." _ * * & Chr(10) & "Please select a Turnaround Report and then restart this macro." * Exit Sub End If Set rTRCell = wsTribalTR.Range("A3") sTRID = rTRCell.Value Set rFoundID = rTribalHist.Find(sTRID, LookIn:=xlValues) 'wsTribalHist.Activate 'rFoundID.Select lTRRow = 3 lHistRow = rFoundID.Row + 2 lHistCol = rFoundID.Column wsTribalHist.Activate wsTribalHist.Range(Cells(lHistRow, lHistCol)).Select <--- Method range of object worksheet failed End Sub Any ideas why it doesn't recognize this range? Thanks! wsTribalHist is a worksheet object therefore there is no point adding the activesheet as it will error just the same, I believe the problem is caused because the workbook is not active and thats what needs to be activated before the range is selected. James |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't select range
On Fri, 25 Jul 2008 10:23:00 -0700, XP wrote:
I haven't tested it all the way through but I think you may need to change this line: wsTribalHist.Range(Cells(lHistRow, lHistCol)).Select to this: wsTribalHist.ActiveSheet.Cells(lHistRow, lHistCol).Select Hope this helps. "salgud" wrote: The following code works until the last line: Option Explicit Public Sub TribalInvCheck() Dim wbTribalHist As Workbook Dim wbTribalTR As Workbook Dim wsTribalTR As Worksheet Dim wsTribalHist As Worksheet Dim rTRCell As Range Dim lTRRow As Long Dim lHistRow As Long Dim rFoundID As Range Dim sTRID As String Dim rTribalHist As Range Dim lHistCol As Long Set wbTribalHist = ThisWorkbook Set wbTribalTR = ActiveWorkbook Set wsTribalTR = ActiveSheet Set wsTribalHist = wbTribalHist.Worksheets("Historical") 'Range set temporarily until I can find out how to set the range to the longest column in the range Set rTribalHist = wsTribalHist.Range("A3:Iv150") 'Application.ScreenUpdating = False If ThisWorkbook.Name = ActiveWorkbook.Name Then MsgBox "Please do not run this macro from the workbook that contains it." _ & Chr(10) & "Please select a Turnaround Report and then restart this macro." Exit Sub End If Set rTRCell = wsTribalTR.Range("A3") sTRID = rTRCell.Value Set rFoundID = rTribalHist.Find(sTRID, LookIn:=xlValues) 'wsTribalHist.Activate 'rFoundID.Select lTRRow = 3 lHistRow = rFoundID.Row + 2 lHistCol = rFoundID.Column wsTribalHist.Activate wsTribalHist.Range(Cells(lHistRow, lHistCol)).Select <--- Method range of object worksheet failed End Sub Any ideas why it doesn't recognize this range? Thanks! Thanks, that did it. Forgot to try a "." instead of a "(". Sometimes that's the trick! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't select range
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can change range to select active rows instead of :=Range("S10 | Excel Discussion (Misc queries) | |||
macro to select range from active cell range name string | Excel Programming | |||
When entering data into a range of cells, select the entire range. | Excel Discussion (Misc queries) | |||
Compare a selected Range with a Named range and select cells that do not exist | Excel Programming | |||
Select Sheet then Select Range | Excel Programming |