VBA Run-time error '1004'

[AlwaysLearning2018]

Hi All,

I have a spreadsheet with 2 macros and 2 sheets within the workbook (sheet 1 and sheet 2). I am very junior with VBA so apologies in advance. I have the below in which I am receiving this run-time error and the "If Range("D2").Offset(num - 1, 0).Value > 0 Then" row is highlighted yellow for this error. Also below are my headers and data in my sheet. I believe the problem is I am basically trying to run an identical macro in sheet 2 that I am trying to run in sheet 1, but with a few modifications (in the below in macro 2, I am working with range "G2" instead and the offset would be .Offset(num -1, 2).Value = "-").

Is there a way in the below variable to first activate the sheet? I've been trying to figure out on my own and read about activating the sheet you are currently working with because excel doesn't know your object.

Any help would be GREATLY appreciated!!! Thank you.



Dim nums As Long, num As Long
nums = Range("D2").End(xlDown).Row
For num = 1 To nums
If Range("D2").Offset(num - 1, 0).Value > 0 Then
Range("D2").Offset(num - 1, 5).Value = "+"


[TABLE="width: 778"]
<colgroup><col width="110" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4022;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;"> <col width="190" style="width: 143pt; mso-width-source: userset; mso-width-alt: 6948;"> <col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <col width="95" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3474;"> <col width="194" style="width: 146pt; mso-width-source: userset; mso-width-alt: 7094;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="128" style="width: 96pt; mso-width-source: userset; mso-width-alt: 4681;"> <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: 82, 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: 128, bgcolor: transparent"]Cash Plus or Minus[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4/30/2019[/TD]
[TD="bgcolor: transparent, align: right"]4444[/TD]
[TD="bgcolor: transparent"]111111USD[/TD]
[TD="bgcolor: transparent, align: right"]450[/TD]
[TD="bgcolor: transparent"]USD[/TD]
[TD="bgcolor: transparent"]111111HKD[/TD]
[TD="bgcolor: transparent, align: right"]0.00[/TD]
[TD="bgcolor: transparent"]HKD[/TD]
[TD="bgcolor: transparent"]+[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlwaysLearning2018]

Hi Justyna,

Sorry to bother again. Last question I will ask on this subject. I am getting an error that Excel cannot complete this task with available resources, but only when I just have 1 line of data to paste. I can click ok a TON of times and what happens is all of my headers turn to 0. Does this maybe have something to do with my offset being - 1, 0 (my full macro is below). If I at least 2 rows of data (outside of my headers) my macro runs as intended. Any suggestions? Thank you very much!!!

[TABLE="width: 766"]
<colgroup><col width="110" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4022;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;"> <col width="190" style="width: 143pt; mso-width-source: userset; mso-width-alt: 6948;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <col width="95" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3474;"> <col width="194" style="width: 146pt; mso-width-source: userset; mso-width-alt: 7094;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="124" style="width: 93pt; mso-width-source: userset; mso-width-alt: 4534;"> <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]


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

 

[JustynaMK]

Hi, sorry for a late reply - any chance you could share part of your file where the problem occurs? I cannot run your macro on my side as it automatically displays "Variable not defined" error on a number of variables - row_number, PortfolioID, nums, num.
It would be easier to analyse your file (with one row of data) in order to see where the error occurs (i.e. which line returns an error in VBA). Thanks.