I'm trying to get the last column of a range. I copied the line of
code directly from a Dave Petersen post in this NG and substituted my
range variable and column variable. But I get an Obj var or with block
variable not set. Both variables are declared and the range has been
checked. So what's up this time?

Option Explicit
Public bHdr As Boolean
Public lTop As Long
Public rFirstData As Range
Public lLastCol As Long
Public lNumbrCol As Long




Sub Extr10L()
Dim wbCtyData As Workbook
Dim oWS As Object
Dim wsTop10List As Worksheet
Dim wsCtyData As Worksheet
Dim lFirstDataRow As Long
Dim lArea1FirstRow As Long
Dim lArea2FirstRow As Long
Dim lArea3FirstRow As Long
Dim lHdrRow As Long
Dim lFirstDataCol As Long
Dim wsTop As Worksheet

Set wsTop10List = ThisWorkbook.Worksheets("CtyLst")
Set wsCtyData = ActiveSheet
Set wbCtyData = ActiveWorkbook

'Test is Mark Top 10 workbook is active
If ActiveWorkbook.Name = ThisWorkbook.Name Then
MsgBox "You have selected the workbook that contains the macro." &
_
Chr(13) & "Please click Ok and select the correct workbook and " &
_
Chr(13) & "worksheet and restart the macro.", vbOKOnly
Exit Sub
End If

'TEST for existing sheet named "Top"
For Each oWS In wbCtyData.Sheets
If oWS.Name = "Top" Then
If MsgBox("A worksheet named Top already exists in this
workbook." _
& Chr(13) & "Please remove or rename it and run the macro
again.", _
vbOKOnly) = vbOK Then Exit Sub
End If

Next

With rFirstData
lLastCol = .Columns(.Columns.Count).Column '<---- OBJ VAR OR
WITH BLOCK VAR NOT SET

End With

I don't see where you used any code to set rFirstData to some range.



davegb wrote:

I'm trying to get the last column of a range. I copied the line of
code directly from a Dave Petersen post in this NG and substituted my
range variable and column variable. But I get an Obj var or with block
variable not set. Both variables are declared and the range has been
checked. So what's up this time?

Option Explicit
Public bHdr As Boolean
Public lTop As Long
Public rFirstData As Range
Public lLastCol As Long
Public lNumbrCol As Long

Sub Extr10L()
Dim wbCtyData As Workbook
Dim oWS As Object
Dim wsTop10List As Worksheet
Dim wsCtyData As Worksheet
Dim lFirstDataRow As Long
Dim lArea1FirstRow As Long
Dim lArea2FirstRow As Long
Dim lArea3FirstRow As Long
Dim lHdrRow As Long
Dim lFirstDataCol As Long
Dim wsTop As Worksheet

Set wsTop10List = ThisWorkbook.Worksheets("CtyLst")
Set wsCtyData = ActiveSheet
Set wbCtyData = ActiveWorkbook

'Test is Mark Top 10 workbook is active
If ActiveWorkbook.Name = ThisWorkbook.Name Then
MsgBox "You have selected the workbook that contains the macro." &
_
Chr(13) & "Please click Ok and select the correct workbook and " &
_
Chr(13) & "worksheet and restart the macro.", vbOKOnly
Exit Sub
End If

'TEST for existing sheet named "Top"
For Each oWS In wbCtyData.Sheets
If oWS.Name = "Top" Then
If MsgBox("A worksheet named Top already exists in this
workbook." _
& Chr(13) & "Please remove or rename it and run the macro
again.", _
vbOKOnly) = vbOK Then Exit Sub
End If

Next

With rFirstData
lLastCol = .Columns(.Columns.Count).Column '<---- OBJ VAR OR
WITH BLOCK VAR NOT SET

End With

--

Dave Peterson

On Dec 10, 12:15 pm, Dave Peterson wrote:
I don't see where you used any code to set rFirstData to some range.

Thanks for your reply, Dave. rFirstData is brought in from the
userform. I've checked and it shows correctly.
Any other ideas?





davegb wrote:

I'm trying to get the last column of a range. I copied the line of
code directly from a Dave Petersen post in this NG and substituted my
range variable and column variable. But I get an Obj var or with block
variable not set. Both variables are declared and the range has been
checked. So what's up this time?

Option Explicit
Public bHdr As Boolean
Public lTop As Long
Public rFirstData As Range
Public lLastCol As Long
Public lNumbrCol As Long

Sub Extr10L()
Dim wbCtyData As Workbook
Dim oWS As Object
Dim wsTop10List As Worksheet
Dim wsCtyData As Worksheet
Dim lFirstDataRow As Long
Dim lArea1FirstRow As Long
Dim lArea2FirstRow As Long
Dim lArea3FirstRow As Long
Dim lHdrRow As Long
Dim lFirstDataCol As Long
Dim wsTop As Worksheet

Set wsTop10List = ThisWorkbook.Worksheets("CtyLst")
Set wsCtyData = ActiveSheet
Set wbCtyData = ActiveWorkbook

'Test is Mark Top 10 workbook is active
If ActiveWorkbook.Name = ThisWorkbook.Name Then
MsgBox "You have selected the workbook that contains the macro." &
_
Chr(13) & "Please click Ok and select the correct workbook and " &
_
Chr(13) & "worksheet and restart the macro.", vbOKOnly
Exit Sub
End If

'TEST for existing sheet named "Top"
For Each oWS In wbCtyData.Sheets
If oWS.Name = "Top" Then
If MsgBox("A worksheet named Top already exists in this
workbook." _
& Chr(13) & "Please remove or rename it and run the macro
again.", _
vbOKOnly) = vbOK Then Exit Sub
End If

Next

With rFirstData
lLastCol = .Columns(.Columns.Count).Column '<---- OBJ VAR OR
WITH BLOCK VAR NOT SET

End With

--

Dave Peterson- Hide quoted text -

- Show quoted text -

On Dec 10, 12:51 pm, davegb wrote:
On Dec 10, 12:15 pm, Dave Peterson wrote:

I don't see where you used any code to set rFirstData to some range.

Thanks for your reply, Dave. rFirstData is brought in from the
userform. I've checked and it shows correctly.
Any other ideas?

Nevermind! I had this code before the userform, so rFirstData isn't
available yet. I moved it down to after the userform is called, and it
works fine. Thanks as always!






davegb wrote:

I'm trying to get the last column of a range. I copied the line of
code directly from a Dave Petersen post in this NG and substituted my
range variable and column variable. But I get an Obj var or with block
variable not set. Both variables are declared and the range has been
checked. So what's up this time?

Option Explicit
Public bHdr As Boolean
Public lTop As Long
Public rFirstData As Range
Public lLastCol As Long
Public lNumbrCol As Long

Sub Extr10L()
Dim wbCtyData As Workbook
Dim oWS As Object
Dim wsTop10List As Worksheet
Dim wsCtyData As Worksheet
Dim lFirstDataRow As Long
Dim lArea1FirstRow As Long
Dim lArea2FirstRow As Long
Dim lArea3FirstRow As Long
Dim lHdrRow As Long
Dim lFirstDataCol As Long
Dim wsTop As Worksheet

Set wsTop10List = ThisWorkbook.Worksheets("CtyLst")
Set wsCtyData = ActiveSheet
Set wbCtyData = ActiveWorkbook

'Test is Mark Top 10 workbook is active
If ActiveWorkbook.Name = ThisWorkbook.Name Then
MsgBox "You have selected the workbook that contains the macro." &
_
Chr(13) & "Please click Ok and select the correct workbook and " &
_
Chr(13) & "worksheet and restart the macro.", vbOKOnly
Exit Sub
End If

'TEST for existing sheet named "Top"
For Each oWS In wbCtyData.Sheets
If oWS.Name = "Top" Then
If MsgBox("A worksheet named Top already exists in this
workbook." _
& Chr(13) & "Please remove or rename it and run the macro
again.", _
vbOKOnly) = vbOK Then Exit Sub
End If

Next

With rFirstData
lLastCol = .Columns(.Columns.Count).Column '<---- OBJ VAR OR
WITH BLOCK VAR NOT SET

End With

--

Dave Peterson- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -