HeadAgainstABrickWall
New Member
- Joined
- Jan 30, 2022
- Messages
- 30
- Office Version
- 365
- Platform
- Windows
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:
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.
Current code is:
VBA Code:
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.