Excel Cannot Complete this Task with Available Resources VBA error

A

AlwaysLearning2018

Board Regular
Joined
Nov 23, 2018
Messages
51
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.



[TABLE="width: 766"]
<tbody>[TR]
[TD="width: 110, bgcolor: transparent"]Settlement Date[/TD]
[TD="width: 62, bgcolor: transparent"]Portfolio[/TD]
[TD="width: 190, bgcolor: transparent"]Bank Account Number (Long)[/TD]
[TD="width: 71, bgcolor: transparent"]Long Units[/TD]
[TD="width: 95, bgcolor: transparent"]Long Currency[/TD]
[TD="width: 194, bgcolor: transparent"]Bank Account Number (Short)[/TD]
[TD="width: 75, bgcolor: transparent"]Short Units[/TD]
[TD="width: 99, bgcolor: transparent"]Short Currency[/TD]
[TD="width: 124, bgcolor: transparent"]Cash Plus or Minus[/TD]
[/TR]
</tbody>[/TABLE]


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 a moderator:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
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:
Upvote 0
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!!
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
Re: Excel Cannot Complete this Task with Available Resources VBA error Help

AlwaysLearning2018 said:
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
Click to expand...

You're welcome,
Regards, JLG
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Copy and paste data through multiple workbooks
Replies
2
Views
507
CDaviess
C
F
  • Solved
Web search using Excel VBA error and script corrections
2
Replies
10
Views
239
Edgar_
Edgar_
I
  • Solved
Out of memory Run-time 7 Transferring data
2
Replies
16
Views
543
Alex Blakenburg
A
SunnyAlv
  • Solved
Hidden Rows VBA
Replies
9
Views
600
SunnyAlv
SunnyAlv
B
  • Question Question
VBA code to cut and paste multiple filtered rows based on multiple criteria
Replies
2
Views
1K
Alex Blakenburg
A

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,021
Latest member
pingpong7117

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top