Error 1004

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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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

Forum statistics

Threads
1,219,006
Messages
6,145,714
Members
450,635
Latest member
Rookie3510

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