Select first cell in offset column?

[HeadAgainstABrickWall]

Hi all, I have a loop that runs through to add a new column after the columns in the array. I'm just struggling to find the right function to select the first cell in the newly-created blank column, recognising that the range is a column and not a cell. I have tried offset and offset+cell but obviously am getting errors.

Current code is:

For Each col In Array("H", "K")

        Set Rng = ActiveSheet.Columns(col)

        With Rng

            .Offset(, 1).EntireColumn.Insert

            .TextToColumns Destination:=Rng.Offset(, 1), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, FieldInfo:=Array(1, 3), TrailingMinusNumbers:=True

            .EntireColumn.DELETE

        End With    

Next

This is being adapted from an in-place text to columns to force-recognise dates in AU format - this has stopped working and I would rather add a new column, TTC the date format and delete the original column rather than guess whether this code will work in one workbook but not another.

 

[Rick Rothstein]

I presume you are talking about the Destination argument for TextToColumns. Change the assigned expression from the one you have to this one...

Rng.Offset(, 1).EntireColumn.Cells(1)

Note: I left the Rng in the expression mainly so the "dot" in front of Offset would not get "lost", but you can eliminate the Rng because your code line is running inside a With block where Rng is its object.

 

[HeadAgainstABrickWall]

I presume you are talking about the Destination argument for TextToColumns. Change the assigned expression from the one you have to this one...

Rng.Offset(, 1).EntireColumn.Cells(1)

Note: I left the Rng in the expression mainly so the "dot" in front of Offset would not get "lost", but you can eliminate the Rng because your code line is running inside a With block where Rng is its object.

Cheers mate, I knew it would be something that simple, I missed the EntireColumn between the two functions. Much appreciated!