Skip to Content
author's profile photo
Former Member

vba runtime error

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Best Answer
    author's profile photo
    Former Member
    Posted on Sep 27, 2007 at 10:18 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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