Error 438 on With statement
 

I am getting the run-time 438 error on the below code:

Sub loan_cap()

' REFRESHES LOAN CAPS

Application.ScreenUpdating = False

Dim Here As Workbook, Caps1 As Workbook, Caps2 As Workbook, Caps3 As Workbook
Set Here = ThisWorkbook

Workbooks.Open "xxx.xls"
Workbooks.Open "xxx.xls"
Workbooks.Open "xxx.xls"

Set Caps1 = Workbooks("$75K-$100K+ Deals by member.xls")
Set Caps2 = Workbooks("$75K-$100K+ Deals by transaction.xls")
Set Caps3 = Workbooks("$75K-$100K+ Deals by transaction2.xls")

Dim LC As Worksheet, There As Worksheet
Set LC = Here.Sheets("LoanCaps")
Dim rng As Range

With Here.LC
.Range("LOANCAP1").ClearContents
.Range("LOANCAP2").ClearContents
.Range("LOANCAP3").ClearContents
.Range("LOANCAP4").ClearContents
.Range("LOANCAP5").ClearContents
.Range("LOANCAP6").ClearContents
.Range("LOANCAP7").ClearContents
End With

I keep getting the error at the beginning of the With statement. I'm
wondering if, since I opened three other workbooks, is there a conflict the
what Excel thinks "Here" is?

Thanks in advance!

Error 438 on With statement
 

With Here.LC

should be

With LC


--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility

http://www.higherdata.com/sql/batchsqlfromexcel.html
Create batch SQL from Excel

"tmwilkin" wrote in message
...
I am getting the run-time 438 error on the below code:

Sub loan_cap()

' REFRESHES LOAN CAPS

Application.ScreenUpdating = False

Dim Here As Workbook, Caps1 As Workbook, Caps2 As Workbook, Caps3 As
Workbook
Set Here = ThisWorkbook

Workbooks.Open "xxx.xls"
Workbooks.Open "xxx.xls"
Workbooks.Open "xxx.xls"

Set Caps1 = Workbooks("$75K-$100K+ Deals by member.xls")
Set Caps2 = Workbooks("$75K-$100K+ Deals by transaction.xls")
Set Caps3 = Workbooks("$75K-$100K+ Deals by transaction2.xls")

Dim LC As Worksheet, There As Worksheet
Set LC = Here.Sheets("LoanCaps")
Dim rng As Range

With Here.LC
.Range("LOANCAP1").ClearContents
.Range("LOANCAP2").ClearContents
.Range("LOANCAP3").ClearContents
.Range("LOANCAP4").ClearContents
.Range("LOANCAP5").ClearContents
.Range("LOANCAP6").ClearContents
.Range("LOANCAP7").ClearContents
End With

I keep getting the error at the beginning of the With statement. I'm
wondering if, since I opened three other workbooks, is there a conflict
the
what Excel thinks "Here" is?

Thanks in advance!

Error 438 on With statement
 

Change...

With Here.LC
- to -
With LC
--
Jim Cone
Portland, Oregon USA



"tmwilkin"

wrote in message
I am getting the run-time 438 error on the below code:

Sub loan_cap()
' REFRESHES LOAN CAPS
Application.ScreenUpdating = False
Dim Here As Workbook, Caps1 As Workbook, Caps2 As Workbook, Caps3 As Workbook
Set Here = ThisWorkbook
Workbooks.Open "xxx.xls"
Workbooks.Open "xxx.xls"
Workbooks.Open "xxx.xls"
Set Caps1 = Workbooks("$75K-$100K+ Deals by member.xls")
Set Caps2 = Workbooks("$75K-$100K+ Deals by transaction.xls")
Set Caps3 = Workbooks("$75K-$100K+ Deals by transaction2.xls")
Dim LC As Worksheet, There As Worksheet
Set LC = Here.Sheets("LoanCaps")
Dim rng As Range

With Here.LC
.Range("LOANCAP1").ClearContents
.Range("LOANCAP2").ClearContents
.Range("LOANCAP3").ClearContents
.Range("LOANCAP4").ClearContents
.Range("LOANCAP5").ClearContents
.Range("LOANCAP6").ClearContents
.Range("LOANCAP7").ClearContents
End With

I keep getting the error at the beginning of the With statement. I'm
wondering if, since I opened three other workbooks, is there a conflict the
what Excel thinks "Here" is?
Thanks in advance!