runtime error '1004': method 'range' of object '_global' failed

[mikura]

Hi

Hope you can help with this <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> problem!

I included a code for send automated emails when the product quantity become 0. And it is working fine up to 99th column but when it reaches 100 column debugger is giving a message "Run-time error '1004': Method 'Range' of object '_Global' failed".

From 101 column excel is returning wrong values than expected

I included the code in workbook (not worksheet)

I've highlighted in red where Excel shows the error:

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim lngResponse As Long
    Dim URL As String, strEmail As String, strSubject As String
    If Left(Target.Address, 2) = "$D" Then
        If Target.Value = "0" Then

[COLOR=#b22222]            lngResponse = MsgBox(Range("$B" & Right(Target.Address, 2)).Value & " (" & Range("$A" & Right(Target.Address, 2)).Value & ") is out of stock. Would you like to send notification?", vbYesNo)[/COLOR]
            If lngResponse = vbYes Then
                strEmail = "xyz@company.com"
                strCc = "email2@company.com"
                strSubject = "MPR Notification " & Range("$B" & Right(Target.Address, 2)).Value & "-Out of Stock"
                strSubject = Application.WorksheetFunction.Substitute(strSubject, " ", "%20")
                strBody = "Please be informed " & Range("$B" & Right(Target.Address, 2)).Value & " (" & Range("$A" & Right(Target.Address, 2)).Value & ") is Out of Stock. %0ARemove this item from online stores immediately."
                strURL = "mailto:" & strEmail & "?cc=" & strCc & "&subject=" & strSubject & "&body=" & strBody
                ShellExecute 0&, vbNullString, strURL, vbNullString, vbNullString, vbNormalFocus
            End If
        End If
    End If
End Sub

I've got no idea how to fix it so any help to just make it go away would be appreciated!

Any help much appreciated as ever!

Thanks

 

[shg]

lngResponse = MsgBox(Cells(target.Row, "B").Value & " (" & Cells(target.Row, "A").Value & ") is out of stock. Would you like to send notification?", vbYesNo)

 

[mikura]

It worked!!!! Thank you so much

 

[shg]

You're welcome.

 

[latoya13]

Hi

Hope you can help with this VBA error I am receiving-

The report that I am using changed its format and added an extra column- I change the report and added the column and now i am receiving this error

"runtime error '1004': method 'range' of object '_global' failed

This is the code and I have highlighted where the error is referring to:

Sheets("BFALL3").Select

'Sort by Ticket Owner, then by tech name, and then by Urgency - Red, Yellow, Green
Sheets("BFALL3").Select
Range("Table12[[#Headers],[TICKET]]").Select
ActiveWorkbook.Worksheets("BFALL3").ListObjects("Table2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("BFALL3").ListObjects("Table2").Sort.SortFields.Add _
Key:=Range("Table2[Ticket Owner]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("BFALL3").ListObjects("Table2").Sort.SortFields.Add _
Key:=Range("Table2[Ticket Points]"), SortOn:=xlSortOnValues, Order:= _
xlDescending, DataOption:=xlSortNormal
' ActiveWorkbook.Worksheets("BFALL3").ListObjects("Table2").Sort.SortFields.Add _
' Key:=Range( _
' "Table2[Ticket Priority Sequence (Low Number is Highest Priority)]"), SortOn _
' :=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("BFALL3").ListObjects("Table2").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' End of Sort

Thanks for your assistance