Error 1004

L

lafrenie

Board Regular
Joined
May 30, 2007
Messages
60
I have the below code but when I get to the line "Range("B5").Select" I get an error message. Does anybody have an idea why ?


Sub Macrosousrach()
ActiveWorkbook.Activate
Sheets(1).Name = "Data"
Range("A:C,F:G,I:P,R:V").Select
Range("R1").Activate
Selection.Delete Shift:=xlToLeft
Range("D2").Select
Columns("D:D").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Style = "Comma"
Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.NumberFormat = _
"_-* #,##0.0 _€_-;-* #,##0.0 _€_-;_-* ""-""?? _€_-;_-@_-"
Selection.NumberFormat = "_-* #,##0 _€_-;-* #,##0 _€_-;_-* ""-""?? _€_-;_-@_-"
Selection.Insert Shift:=xlToRight
Range("D1").Select
ActiveCell.FormulaR1C1 = "Montant S/R"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=+IF((RC[-1]=""R""),(RC[1]*-1),RC[1])"
Range("D2").Select
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("d2").AutoFill Destination:=Range("d2:d" & LR)
Selection.NumberFormat = "#,##0_);[Red](#,##0)"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R30000C6").CreatePivotTable TableDestination:="", _
TableName:="Tableau croisé dynamique1", DefaultVersion:= _
xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
"Code Apporteur")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("Tableau croisé dynamique1").AddDataField ActiveSheet. _
PivotTables("Tableau croisé dynamique1").PivotFields("Montant S/R"), _
"Somme de Montant S/R", xlSum
ActiveWorkbook.ShowPivotTableFieldList = False



Range("B5").Select






Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"
Selection.Sort Key1:="R5C2:R51C2", Order1:=xlAscending, Type:= _
xlSortValues, OrderCustom:=1, Orientation:=xlTopToBottom
Dim LR2 As Long


LR2 = Range("A" & Rows.Count).End(xlUp).Row
Range("B" & LR2).Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"

End Sub
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
I'm not sure why you get that error, but there is no need for any selecting:

Code: 
    With Range(Range("B5"), Range("B5").End(xlDown))
        .NumberFormat = "#,##0_);[Red](#,##0)"
        .Sort Key1:="R5C2:R51C2", Order1:=xlAscending, Type:= _
        xlSortValues, OrderCustom:=1, Orientation:=xlTopToBottom
    End With
    Dim LR2 As Long
    LR2 = Range("A" & Rows.Count).End(xlUp).Row
    Range("B" & LR2).NumberFormat = "#,##0_);[Red](#,##0)"
 
Upvote 0
I still get Execution Error 1004.


Sub Macrosousrach()
ActiveWorkbook.Activate
Sheets(1).Name = "Data"


Range("A:C,F:G,I:P,R:V").Select
Range("R1").Activate
Selection.Delete Shift:=xlToLeft
Range("D2").Select
Columns("D:D").Select
Selection.Replace What:="*", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Style = "Comma"
Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.NumberFormat = _
"_-* #,##0.0 _€_-;-* #,##0.0 _€_-;_-* ""-""?? _€_-;_-@_-"
Selection.NumberFormat = "_-* #,##0 _€_-;-* #,##0 _€_-;_-* ""-""?? _€_-;_-@_-"
Selection.Insert Shift:=xlToRight
Range("D1").Select
ActiveCell.FormulaR1C1 = "Montant S/R"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=+IF((RC[-1]=""R""),(RC[1]*-1),RC[1])"
Range("D2").Select
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("d2").AutoFill Destination:=Range("d2:d" & LR)
Selection.NumberFormat = "#,##0_);[Red](#,##0)"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R30000C6").CreatePivotTable TableDestination:="", _
TableName:="Tableau croisé dynamique1", DefaultVersion:= _
xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
"Code Apporteur")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("Tableau croisé dynamique1").AddDataField ActiveSheet. _
PivotTables("Tableau croisé dynamique1").PivotFields("Montant S/R"), _
"Somme de Montant S/R", xlSum
ActiveWorkbook.ShowPivotTableFieldList = False





Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"
Selection.Sort Key1:="R5C2:R51C2", Order1:=xlAscending, Type:= _
xlSortValues, OrderCustom:=1, Orientation:=xlTopToBottom
Dim LR2 As Long


LR2 = Range("A" & Rows.Count).End(xlUp).Row
Range("B" & LR2).Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"

End Sub
 
Upvote 0
I have and its still not working Error 1004.

Sub Macrosousrach()
ActiveWorkbook.Activate
Sheets(1).Name = "Data"

Range("A:C,F:G,I:P,R:V").Select
Range("R1").Activate
Selection.Delete Shift:=xlToLeft
Range("D2").Select
Columns("D:D").Select
Selection.Replace What:="*", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Style = "Comma"
Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.NumberFormat = _
"_-* #,##0.0 _€_-;-* #,##0.0 _€_-;_-* ""-""?? _€_-;_-@_-"
Selection.NumberFormat = "_-* #,##0 _€_-;-* #,##0 _€_-;_-* ""-""?? _€_-;_-@_-"
Selection.Insert Shift:=xlToRight
Range("D1").Select
ActiveCell.FormulaR1C1 = "Montant S/R"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=+IF((RC[-1]=""R""),(RC[1]*-1),RC[1])"
Range("D2").Select
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("d2").AutoFill Destination:=Range("d2:d" & LR)
Selection.NumberFormat = "#,##0_);[Red](#,##0)"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R30000C6").CreatePivotTable TableDestination:="", _
TableName:="Tableau croisé dynamique1", DefaultVersion:= _
xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
"Code Apporteur")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("Tableau croisé dynamique1").AddDataField ActiveSheet. _
PivotTables("Tableau croisé dynamique1").PivotFields("Montant S/R"), _
"Somme de Montant S/R", xlSum
ActiveWorkbook.ShowPivotTableFieldList = False




With Range(Range("B5"), Range("B5").End(xlDown))
.NumberFormat = "#,##0_);[Red](#,##0)"
.Sort Key1:="R5C2:R51C2", Order1:=xlAscending, Type:= _
xlSortValues, OrderCustom:=1, Orientation:=xlTopToBottom
End With
Dim LR2 As Long
LR2 = Range("A" & Rows.Count).End(xlUp).Row
Range("B" & LR2).NumberFormat = "#,##0_);[Red](#,##0)"


End Sub
 
Upvote 0
The error message is on line:


.Sort Key1:="R5C2:R51C2", Order1:=xlAscending, Type:= _
xlSortValues, OrderCustom:=1, Orientation:=xlTopToBottom

I also think that:

With Range(Range("B5"), Range("B5").End(xlDown))

is not selecting the range in which I want because the:
.NumberFormat = "#,##0_);[Red](#,##0)"

is not applield to the selected range.

Thks for your help.
 
Upvote 0
Your sort key reference is not valid, and there is a bug in how Excel interprets the NumberFormat. Try:

Code: 
    With Range(Range("B5"), Range("B5").End(xlDown))
        .NumberFormat = "#,##0_);[Red](#,##0);0_)"
        .Sort Key1:=.Cells(1, 1), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End With
 
Upvote 0
I dont have an error message but it is not modifying the data in Colum B of the pivot table. What I bassically want is to sort the data ascending and the all numbers inferior to 0 in red.

Can you help ?
 
Upvote 0
To sort the data in a pivottable, right click a row field and choose Field Settings. Click Advanced, check an AutoSort option, select the field to use and click OK twice. If you record your actions you will get some code like this:

Code: 
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Name").AutoSort xlAscending, "Sum of Val2"
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Help with Vba Code
Replies
3
Views
157
Coyotex3
C
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
958
Joe4
Joe4
L
  • Locked
  • Question
Help Cleaning Up Code
Replies
2
Views
331
RoryA
RoryA
P
  • Solved
Understanding VBA Macro
Replies
5
Views
306
RoryA
RoryA
tezza
  • Question
Option explicit workbook help
Replies
3
Views
462
igold
igold

Forum statistics

Threads
1,214,833
Messages
6,121,862
Members
449,052
Latest member
Fuddy_Duddy

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