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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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)"
 

lafrenie

Board Regular
Joined
May 30, 2007
Messages
60
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
 

lafrenie

Board Regular
Joined
May 30, 2007
Messages
60

ADVERTISEMENT

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
 

lafrenie

Board Regular
Joined
May 30, 2007
Messages
60

ADVERTISEMENT

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.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
 

lafrenie

Board Regular
Joined
May 30, 2007
Messages
60
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 ?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,774
Members
413,934
Latest member
austinb

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
Top