0

I am trying to convert a series of dates from text to 'Date' format, that are in column B on the 'Master' sheet.

I created a macro but I want to run it from any Worksheet rather than being on the Master worksheet. However, the error code 'Subscript out of range' keeps showing up, highlighting With Sheets("sh2"). The code I have so far is:

Sub DateFormatUpdate()

    DateFormatUpdate Macro
    Updates the format of the dates in the master data sheet from 'General' to 'Date'

    Dim sh2 As Worksheet
    Set sh2 = ActiveWorkbook.Sheets("Master")

    With Sheets("sh2")
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True
    Range("C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("C2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True
    Range("R9").Select
    End With

End Sub

I'm quite new with VBA so my apologies if this is actually an easy problem! Thank you for the help!

YowE3K
  • 23,852
  • 7
  • 26
  • 40
Joon10
  • 1
  • 1
    You have `With` statements, but never anchor your ranges with it. Within `With`, you need to use `.` to anchor `Range`/`Cells` to that sheet/variable. I.e. `With sh2 // .Range("B2").Select ...`. Also, a note, it's best to [avoid using `.Select/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – BruceWayne Oct 31 '17 at 06:09
  • ^^ plus you can't `Select` a range on a worksheet unless it is the active sheet. Which is another reason to get rid of `Select`, not a reason to `Activate` the worksheet. – YowE3K Oct 31 '17 at 06:16

1 Answers1

0

Instead of

With Sheets("sh2")

try using

With sh2

as you have declared sh2 as the worksheet variable.

shrivallabha.redij
  • 5,832
  • 1
  • 12
  • 27
  • Hello! Thanks for getting back. It then highlights Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _ :=Array(1, 5), TrailingMinusNumbers:=True with the error code 1004: Microsoft Excel can only convert one column at a time. Try again by selecting cells in one column only. – Joon10 Oct 31 '17 at 06:08
  • That is different error. One thing I noticed is that if you delimit your column B then it will overwrite content to column C which you are delimiting next. Is it correct? – shrivallabha.redij Oct 31 '17 at 06:24