Results 1 to 6 of 6

Thread: Excel Cannot Complete this Task with Available Resources VBA error
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2018
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel Cannot Complete this Task with Available Resources VBA error

    Hi All,

    I am getting an "Excel Cannot Complete this Task with Available Resources" message when I attempt to run the below macro. The issue only occurs if I paste 1 line of data in the macro workbook If I have more than 1 line, everything runs as I expect. In my code below, is my If statement offset o num - 1 (If .Range("D2").Offset(num - 1, 0).Value > 0 Then .Range("D2").Offset(num - 1, 5).Value = "+") possibly the problem? Any suggestions would be GREATLY appreciated. Thank you!!! My column headers are below as well.



    Settlement Date Portfolio Bank Account Number (Long) Long Units Long Currency Bank Account Number (Short) Short Units Short Currency Cash Plus or Minus


    Code:
    Private Sub CommandButton1_Click()
    
    On Error Resume Next
    With Range("A1").CurrentRegion
        .AutoFilter 5, "ARS"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "AUD"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "BRL"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "CAD"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "CHF"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "CLP"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "CNY"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "COP"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "CZK"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "DKK"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "EGP"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "EUR"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "GBP"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "GHS"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "HKD"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "HUF"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "ISK"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "INR"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "IDR"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "ILS"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "JPY"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "KRW"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "LAK"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "LBP"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "MKD"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "MYR"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "MXN"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "MXP"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "NOK"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "NZD"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "PKR"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "PLN"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "PEN"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "PHP"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "QAR"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "RUB"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "SAR"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "RSD"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "SGD"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "LKR"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "SEK"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "TWD"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "THB"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "TRY"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "VND"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "ZWD"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 5, "ZAR"
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
    ActiveSheet.ShowAllData
        .AutoFilter 8, "ARS"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "AUD"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "BRL"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "CAD"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "CHF"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "CLP"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "CNY"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "COP"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "CZK"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "DKK"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "EGP"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "EUR"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "GBP"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "GHS"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "HKD"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "HUF"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "ISK"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "INR"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "IDR"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "ILS"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "JPY"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "KRW"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "LAK"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "LBP"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "MKD"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "MYR"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "MXN"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "NOK"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "NZD"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "PKR"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "PLN"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "PEN"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "PHP"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "QAR"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "RUB"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "SAR"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "RSD"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "SGD"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "LKR"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "SEK"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "TWD"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "THB"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "TRY"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "VND"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "ZWD"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        .AutoFilter 8, "ZAR"
        .Columns(7).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "0.00"
        ActiveSheet.ShowAllData
        .Parent.AutoFilterMode = False
        End With
      On Error GoTo 0
      
    row_number = 1
    
    Do
    DoEvents
        row_number = row_number + 1
        PortfolioID = Sheet1.Range("E" & row_number)
            
    If Range("E" & row_number) <> "USD" And Range("H" & row_number) <> "USD" Then
        Sheet1.Rows(row_number).Delete
        
    row_number = row_number - 1
            
    End If
    Loop Until PortfolioID = ""
    row_number = 1
    Do
    DoEvents
        row_number = row_number + 1
        PortfolioID = Sheet1.Range("B" & row_number)
            
    If Range("B" & row_number) = "0418" Or Range("B" & row_number) = "0495" Then
        Sheet1.Rows(row_number).Delete
        
    row_number = row_number - 1
            
    End If
    Loop Until PortfolioID = ""
    
        Range("A2:A20").Select
        Selection.NumberFormat = "yyyymmdd"
        
        Range("D2:D27").Select
        Selection.NumberFormat = "0.00"
        Range("G2:G29").Select
        Selection.NumberFormat = "0.00"
        
    Dim ws As Worksheet, iRow As Long, iCol As Long, i As Long
    Set ws = Sheets("Sheet1")
    For iRow = 1 To 20
        For iCol = 3 To 6
        With ws.Cells(iRow, iCol)
            If .Value = "269505USD" Or .Value = "264061USD" Or .Value = "299501USD" Or .Value = "269994USD" Or .Value = "265292USD" Or .Value = "264084USD" Or .Value = "270020USD" Or .Value = "234109USD" Or .Value = "269502USD" Or .Value = "269501USD" Or .Value = "299517USD" Or .Value = "270005USD" Then .Value = .Value & " - BNY"
            End With
            Next
            Next
            
    With Sheet1
        nums = .Range("D" & .Rows.Count).End(xlUp).Row
        For num = 1 To nums
            If .Range("D2").Offset(num - 1, 0).Value > 0 Then
                .Range("D2").Offset(num - 1, 5).Value = "+"
         End If
       Next num
    End With
    
    
    Dim new_column_order As Variant, new_index As Integer
    Dim found As Range, counter As Integer
    new_column_order = Array("Settlement Date", "Portfolio", "Bank Account Number (Long)", "Long Units", "Cash Plus or Minus", "Long Currency")
    
    counter = 1
    For new_index = LBound(new_column_order) To UBound(new_column_order)
    
    Set found = Rows("1:1").Find(new_column_order(new_index), LookIn:=xlValues, _
    LookAt:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False)
    If Not found Is Nothing Then
        If found.Column <> counter Then
        found.EntireColumn.Cut
        Columns(counter).Insert shift:=xlToRight
        End If
        
    counter = counter + 1
        
    End If
    Next new_index
    
    End Sub
    Last edited by Fluff; May 2nd, 2019 at 02:55 PM. Reason: Code tags

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    11,062
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Excel Cannot Complete this Task with Available Resources VBA error Help

    to Avoid the error message, you can use an If statement like this:
    Code:
    If Range("A1").CurrentRegion.Rows.Count > 2 Then
     'Your Autofilter code here
    End If
    Or If you don't want the code to execute without the autofilter then"
    Code:
    If Range("A1").CurrentRegion.Rows.Count < 2 Then Exit Sub
    Last edited by JLGWhiz; May 2nd, 2019 at 04:03 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    New Member
    Join Date
    Nov 2018
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Cannot Complete this Task with Available Resources VBA error Help

    Hi JLGWhiz,

    Apologies for the ignorant question, but where in my macro would I enter that if statement?

    Thanks!!

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    11,062
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Excel Cannot Complete this Task with Available Resources VBA error Help

    The If statement would precede the Autofilter. I don't know which statement you intend to use. The first one would just skip the Autofilter part of the macro and do all the other stuff. But I don't know if you want to do that if the Autofilter doesn't process. So the second If statement would terminate the macro if there is nothing to filter. In the post it shows you where to put If and End If lines for the first snippet.

    Maybe a better way of saying it is to put the If statement before the 'With Range("A1").CurrentRegion' Statement and the End If after the 'End With' for the first snippet.

    There is no End If for the second statement so it would just go before the With statement if you decide to use it.
    Last edited by JLGWhiz; May 2nd, 2019 at 08:38 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  5. #5
    New Member
    Join Date
    Nov 2018
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Cannot Complete this Task with Available Resources VBA error Help

    Hi JLGWhiz,

    Thank you very much for the explanation. Extremely helpful, and works great. I really appreciate you taking the time to help me. Greatly, greatly appreciated.

    Best Regards

  6. #6
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    11,062
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Excel Cannot Complete this Task with Available Resources VBA error Help

    Quote Originally Posted by AlwaysLearning2018 View Post
    Hi JLGWhiz,

    Thank you very much for the explanation. Extremely helpful, and works great. I really appreciate you taking the time to help me. Greatly, greatly appreciated.

    Best Regards
    You're welcome,
    Regards, JLG
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •