Number format VBA to two different tabs in workbook

LUIS FREUND
LUIS FREUND used Ask the Experts™
on
How can I apply this number format code to different sheets:

On "MAIN_PN" tab I have this code that works great But I need to apply it to "MAIN" tab O27:O30 as well.

Sub FixDates()
   Columns("I").TextToColumns
   Range("I4:J650", Cells(Rows.Count, "I").End(xlUp)).NumberFormat = "mm/dd/yy"
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
You may try something like this...

Sub FixDates()
With Sheets("MAIN_PN")
    .Columns("I").TextToColumns
    .Range("I4:J" & .Cells(Rows.Count, "I").End(xlUp).Row).NumberFormat = "mm/dd/yy"
End With
With Sheets("MAIN")
    .Range("O27:O30").TextToColumns
    .Range("O27:O30").NumberFormat = "mm/dd/yy"
End With
End Sub

Open in new window

Author

Commented:
AWESOME!  MANY THANKS!
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Luis! :)

Author

Commented:
Oh this came up......runtime error

I only get it when there is nothing in column O27:O30.  There are instances that this area will not get populated at times.
C--Users-lfreund-Pictures-error.JPG
C--Users-lfreund-Pictures-vba.JPG
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Okay, try this...
Sub FixDates()
On Error Resume Next
With Sheets("MAIN_PN")
    .Columns("I").TextToColumns
    .Range("I4:J" & .Cells(Rows.Count, "I").End(xlUp).Row).NumberFormat = "mm/dd/yy"
End With
With Sheets("MAIN")
    .Range("O27:O30").TextToColumns
    .Range("O27:O30").NumberFormat = "mm/dd/yy"
End With
End Sub

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial