vba runtime error

[Former Member]

Hi,

Our workbook has three sheets and each sheet is having one query. i written vba code to remove '#' values. But when i am refresh all the queries at once i am getting the runtime error

run-time error '1004'

select method of Range class failed

How to solve this:

<b>this is my code:</b>

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

If queryID = "SAPBEXq0001" Or queryID = "SAPBEXq0002" Or queryID = "SAPBEXq0003" Then

resultArea.Select

'Remove '#'

Selection.Cells.Replace What:="#", Replacement:="", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, MatchByte:=True

'Remove 'Not assigned'

'Selection.Cells.Replace What:="Not assigned", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, MatchByte:=True

End If

[Former Member · Accepted solution]

Please tell us which line creates the error.

If it is the

   resultArea.Select?

If so try following:

I assume SAPBEXq0001 to be on sheet 1, SAPBEXq0002 on sheet 2, SAPBEXq0003 on sheet 3 for this coding:

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)
  
  Dim SheetNo As Double
  Select Case queryID
    Case "SAPBEXq0001"
      SheetNo = 1
    Case "SAPBEXq0002"
      SheetNo = 2
    Case "SAPBEXq0003"
      SheetNo = 3
    Case Else
      Exit Sub
  End Select
'Remove '#'
  Sheets(SheetNo).resultArea.Replace What:="#", Replacement:="", _
  LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
  MatchByte:=True
'Remove 'Not assigned'
  Sheets(SheetNo).resultArea.Replace What:="Not assigned", Replacement:="", _
  LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
  MatchByte:=True
End Sub

hope that helps (and runs, not tested.

br

Andreas

Message was edited by:

Andreas Hinrichs

[Former Member · Accepted solution]

Hi,

I tried your code but i am getting the error "Object doesn't support this property or method"

How to rectify?

Thanks

[Former Member · Accepted solution]

Hi,

please, allways tell what line creates the error... You find it when clicking the Debug Button in the message box.

Nevertheless, there's omly one methode (.replace)

I will check my code, bit this will take a bit. I hope to have a solution within one hour.

br

Andreas

[Former Member · Accepted solution]

ok, took a while, but this works:

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)
  Dim SheetNo As Double
  Select Case queryID
    Case "SAPBEXq0001"
      SheetNo = 1
    Case "SAPBEXq0002"
      SheetNo = 2
    Case "SAPBEXq0003"
      SheetNo = 3
    Case Else
      Exit Sub
  End Select
'Remove '#'
  resultArea.Replace What:="#", Replacement:="", _
    LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
    MatchByte:=True
'Remove 'Not assigned'
  resultArea.Replace What:="Not assigned", Replacement:="", _
    LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
    MatchByte:=True
End Sub

br and a nice weekend

Andreas