subscript out of range

[josros60]

I have a spreadsheet in excel named "writeoff" and I print every month a report from a website in excel format.
then i have a macro to link the report printed from website by the way name of file is "ReportProxyServlet" to cells on writeoff sheet by account number.

But every time i run the macro I am getting this error "run time error 9, subscript out of range".
I am attaching the two files.

here it's the code:

Private Sub CommandButton1_Click()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, nRw As Long, e As Range, fVal As Range
 Set sh1 = ThisWorkbook.Sheets("WRITEOFF") 'Edit sheet name
 Set sh2 = Workbooks("ReportProxyServlet").Sheets("ReportProxyServlet") 'Edit workbook and sheet name
 lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
 nRw = sh2.Cells(Rows.Count, 1).End(xlUp).Row
 Set rng = sh1.Range("A2:A" & lr)
 For Each e In rng
 Set fVal = sh2.Range("A2:A" & nRw).Find(e.Value, LookIn:=xlValues)
 If Not fVal Is Nothing Then
 sh1.Range("E" & e.Row) = fVal.Offset(0, 2).Value
 End If
 Next
End Sub

thank you

[6StringJazzer]

The workbook ReportProxyServlet is not open when you run this macro. If this file exists you need to open it first to be able to refer to it in the Workbooks collection.

Modify your code to look like this. This assumes that the file is in the same folder as this workbook. If not, modify the call to Open to include the correct path:

   Dim wb As Workbook
   Set wb = Workbooks.Open(ThisWorkbook.Path & "\ReportProxyServlet.xls")
   
   Set sh2 = wb.Sheets("ReportProxyServlet") 'Edit workbook and sheet name

[josros6074]

Thank you.

I modified with your suggestion don't get error anymore but the amounts from "ReportProxyServlet" report doesn't transfer to column e on "WRITEOFF" worksheet.

for exam writeoff sheet has a column with account numbers that match ReportProxyServlet accounts number, so I want the macro to look for the accounts # that have amounts to transfer to writeoff sheet.

thanks again.

[josros6074]

I thought about it, and i think what need help is in the same code to add vlookup function to look for the account find the amount and post it to to writeoff she in the in the right cell.

[6StringJazzer]

Your new question is not related to your original question. You may be a better response by starting a new thread.