VBA Run-time error '1004'

AlwaysLearning2018

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

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

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