0

I am new to VBA. I am currently creating a form for my thesis study. But I bumped to a "Subscript out of range" Error 9. It also don't show "Debug" option in the notification. All is working before I enter that code.

Can anyone help me to solve or redo this? Here is the code:

Sub show_Sale_Available_Data()

Dim dsh As Worksheet
Dim sh As Worksheet

Set dsh = ThisWorkbook.Sheets("Sale_Available")

Set sh = ThisWorkbook.Sheets("Sale_Availabale_Display")

dsh.AutoFilterMode = False

dsh.Range("H:H").NumberFormat = "D-MMM-YYYY"

'''' filter here
dsh.UsedRange.AutoFilter 8, ">=" & Me.txt_Date_start.Value, xlAnd, "<=" & Me.txt_Date_End.Value

If Me.OptionButton2.Value = True Then
dsh.UsedRange.AutoFilter 3, "Add to Stocks"
End If

If Me.OptionButton7.Value = True Then
dsh.UsedRange.AutoFilter 3, "Sale"
End If

sh.UsedRange.Clear

dsh.UsedRange.Copy
sh.Range("A1").PasteSpecial xlpastevalueandnumberformats


''' show data in listbox

Dim lr As Long
lr = Application.WorksheetFunction.CountA(sh.Range("A:A"))

If lr = 1 Then lr = 2

With Me.ListBox2
    .ColumnCount = 8
    .ColumnHeads = True
    .ColumnWidths = "0,150,65,65,65,65,65,65"
    .RowSource = sh.Name & "!A2:H" & lr
End With


End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
Derick Mercado
  • 1
  • You haven't indicated which line, but usually that means a sheet or something doesn't exist. – SJR Nov 18 '20 at 15:04
  • 1
    Is "Sale_Availabale_Display" a typo? – Jonathan Willcock Nov 18 '20 at 15:08
  • 1
    If column `A` is empty then `lr = 0`. – VBasic2008 Nov 18 '20 at 15:08
  • Set a breakpoint on the first line that starts with `Set`, and then step through the code in the debugger. You don't need a debug option in the error message in order to debug the code. When you've narrowed it down to a line of the code, we can try to help. – Ken White Nov 18 '20 at 15:09
  • ^ Or change your error handling mode to Break in Class Module. – BigBen Nov 18 '20 at 15:09
  • See [this](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for the right way to find the last row. – BigBen Nov 18 '20 at 15:09
  • Thankyou guys for the info. I already found my mistake. It is in spelling, instead of Sales_Available, I mistakenly use Sale_Available. But I will try to run it first – Derick Mercado Nov 18 '20 at 16:06

0 Answers0