Change my sheets

[juneau730]

Trying a little humor with the subject.

I recorded the following macro in excel, then shortened/simplified it. If I am in Developer Mode and run it from there, it works great, but.. I am trying to assign the code to a button. When I do and click the button, it appears to be running on the sheet where the button is, not the sheet where my data is.

Searching, I was not able to find how to correct this.

Sub Configure_Dates()    Range("E2:E7500").Select
    Selection.Replace What:=",", Replacement:=", ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("F2:F7500").Select
    Selection.Replace What:="@", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("E2:E7500").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("G2:G7500").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("H2:H7500").Select
    Selection.NumberFormat = "m/d/yyyy"
End Sub

 

[mrshl9898]

You'll need to go to the sheet first, change "sheettorunmacroon" to "Sheet1" or whatever the sheet name is.

Sub Configure_Dates()    

Sheets("sheettorunmacroon").Select
Range("E2:E7500").Select
    Selection.Replace What:=",", Replacement:=", ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("F2:F7500").Select
    Selection.Replace What:="@", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("E2:E7500").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("G2:G7500").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("H2:H7500").Select
    Selection.NumberFormat = "m/d/yyyy"
End Sub

 

[juneau730]

Good morning @mrshl9898, thank you for your help, that did the trick. For some reason, adding that line is causing an error when executed at this part of the marco

[COLOR=#333333]Sub Configure_Dates()    [/COLOR]    Range("E2:E7500").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("G2:G7500").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("H2:H7500").Select
    Selection.NumberFormat = "m/d/yyyy"
End Sub

It doesn't seem to like the Selection.NumberFormat string. If I remove the above, the macro works and my date to whole number formulas still work, though I would like to change the date format.

 

[Fluff]

How about

Sub Configure_Dates()
   With Sheets("[COLOR=#ff0000]Sheetname[/COLOR]")
      .Range("E2:E7500").Replace What:=",", Replacement:=", ", LookAt:=xlPart, _
         SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
         ReplaceFormat:=False
      .Range("F2:F7500").Replace What:="@", Replacement:=" ", LookAt:=xlPart, _
         SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
         ReplaceFormat:=False
      .Range("E2:E7500").NumberFormat = "m/d/yyyy"
      .Range("G2:G7500").NumberFormat = "m/d/yyyy"
      .Range("H2:H7500").NumberFormat = "m/d/yyyy"
   End With
End Sub

Change name in red to suit

 

[juneau730]

Thanks @Fluff, tried that and for whatever reason, it still errors at

[COLOR=#333333]      .Range("E2:E7500").NumberFormat = "m/d/yyyy"[/COLOR]

It works without those three lines, so I can at least move forward with it, until it's figured out why it doesn't like those lines.

 

[Fluff]

What error message do you get?

 

[juneau730]

What error message do you get?

Run-time error '1004':

Unable to set the NumberFormat property of the Range class

 

[Fluff]

Is the sheet protected?

 

[juneau730]

Yes, the sheet is, I have three columns with formulas in them I was protecting.

Interesting, even though the columns with the dates needing the format change were not protected/locked and could be altered manually, formatting was not allowed. Once I removed protection it works, which raises a dilemma, protect the sheet to preserve the formulas or allow date formatting.

Thanks your your help with this, you guys are awesome!

 

[Fluff]

You can unprotect/protect the sheet in code

Sub Configure_Dates()
   With Sheets("pcode")
      .Unprotect Password:="[COLOR=#ff0000]password[/COLOR]"
      .Range("E2:E7500").Replace What:=",", Replacement:=", ", LookAt:=xlPart, _
         SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
         ReplaceFormat:=False
      .Range("F2:F7500").Replace What:="@", Replacement:=" ", LookAt:=xlPart, _
         SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
         ReplaceFormat:=False
      .Range("E2:E7500").NumberFormat = "m/d/yyyy"
      .Range("G2:G7500").NumberFormat = "m/d/yyyy"
      .Range("H2:H7500").NumberFormat = "m/d/yyyy"
      .Protect Password:="[COLOR=#ff0000]password[/COLOR]"
   End With
End Sub

You may need to record a macro of you protecting the sheet, to find the protection you want.