I have an if statement that is needed sometimes but not all the time. It is after an extract that uses a delimited file, but sometimes it is already delimited and others it is not. It depends on if the user in Excel changes the settings in the delimiter. It appears that Excel remembers the last delimited settings and tries to apply that, even if it is not needed.
In todays example my if statement on cell D4 was not honored and it was clearly populated but it still tried to TextToColumn column A when it was not needed. Is there another way that I can set the delimited settings so they are consistent, and then a different check, to see if anything right of column A, has a value? If it comes in with values the delimited file came in with the previous settings that it remember from the last time it ran, but as soon as someone messes with the settings of Excels delimited, it the TextToColumn is needed again.
Any ideas how I can fix this?
If (D4 = "") Then 'if blank? do TextToColumns
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
Array(19, 1)), TrailingMinusNumbers:=True
'if not continue