View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
tmwilkin tmwilkin is offline
external usenet poster
 
Posts: 13
Default 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!