VBA Run-time error '1004'

A

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 = "+"


Settlement DatePortfolioBank Account Number (Long)Long UnitsLong CurrencyBank Account Number (Short)Short UnitsShort CurrencyCash Plus or Minus
4/30/20194444111111USD450USD111111HKD0.00HKD+
<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> </tbody>
 
Sort by date Sort by votes
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!!!

Settlement DatePortfolioBank Account Number (Long)Long UnitsLong CurrencyBank Account Number (Short)Short UnitsShort CurrencyCash Plus or Minus
<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> </tbody>


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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
You must log in or register to reply here.

Similar threads

E
  • Question
VBA for Web Scraping help
Replies
0
Views
583
epactheactor
E
S
  • Question
Error message when executing macro
Replies
3
Views
146
rlv01
R
Y
  • Solved
Highlighting selected words in a column.
Replies
6
Views
131
Akuini
Akuini
U
  • Question
recorded macro throwing 1004 error
Replies
3
Views
361
ursua
U
S
  • Solved
Run-time error 13 in VBA
Replies
5
Views
175
RoryA
RoryA

Forum statistics

Threads
1,214,648
Messages
6,120,726
Members
448,987
Latest member
marion_davis

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