I am running the following code
and this code is being accessed through a network drive by putting a shortcut in the XLSTART folder of two different machines. The problem is that I get the following error
Any suggestions?
Rich (BB code):
Option Explicit
Sub DESCRIPTION_SCRUBBER()
Dim rng As Range, strfirst As String, strletter As String, strnumber As String, LastRow As Long
Dim i As Long, Description As String, objAccess As Object, RowAnswer As String, RowMyNote As String
' START SELECT COLUMN MSGBOX
On Error Resume Next
Set rng = Application.InputBox("Select the top of your descriptions with the mouse, just below the header.", Type:=8)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "You cancelled"
Exit Sub
End If
' END SELECT COLUMN MSGBOX
' START GET COLUMN LETTER
strfirst = IIf(Chr$(64 + rng.Column \ 26) = "@", "", Chr(64 + rng.Column \ 26))
strletter = strfirst & Chr$(64 + rng.Column Mod 26)
strnumber = rng.Row
' END GET COLUMN LETTER
' START MACRO WILL STOP AT ROW _? QUESTION
'Place your text here
RowMyNote = "Your top cell is " & strletter & strnumber & ", is that correct?"
'Display MessageBox
RowAnswer = MsgBox(RowMyNote, vbQuestion + vbYesNo, "???")
If RowAnswer = vbNo Then
'Code for No button Press
Exit Sub
Else
' END MACRO WILL STOP AT ROW _? QUESTION
' OPENS GREGG'S DESCRIPTION_CLEANER DATABASE
Set objAccess = GetObject _
("\\canfsw03\groups3\PricingManagerMaintenance\Description_Cleaner.mdb")
With objAccess
.Visible = False
End With
LastRow = Range(strletter & "65536").End(xlUp).Row
For i = LastRow To strnumber Step -1
Description = objAccess.Run("RplASC_1", Range(strletter & i).Value)
Range(strletter & i).Value = Description
Next
' END IF FOR MACRO WILL STOP AT ROW _? QUESTION
End If
' END IF FOR MACRO WILL STOP AT ROW _? QUESTION
MsgBox "All Done!"
End Sub
on one machine and not on the other. I'm trying to figure out why. The error procedes to highlight this line of codeRun-time error ‘40351’:
Application-defined or object-defined error
Rich (BB code):
Description = objAccess.Run("RplASC_1", Range(strletter & i).Value)