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
Bookmarks