Macros are changing functionality

[Mattfrisbie]

I am using a Macro to separate characters from a string in a cell using text to columns as shown below.

Sub Separate()
'
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=True, OtherChar:= _
"_", FieldInfo:=Array(Array(1, 9), Array(2, 9), Array(3, 9), Array(4, 9), Array(5, 9), _
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)), TrailingMinusNumbers:=True
Range("A1").Select
End Sub


No problems, works great. I noticed excel was trying to anticipate this action and everytime I copy from a .CSV file it tried to separate into columns. I found a way to stop that using the macro below:

Sub CorrectCSVImport()
On Error Resume Next
If IsEmpty(Range("A1")) Then Range("A1") = "XYZZY"
Range("A1").TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
OtherChar:=""
If Range("A1") = "XYZZY" Then Range("A1") = ""
If Err.Number <> 0 Then MsgBox Err.Description

End Sub

Now when I copy down formulas (drag down from lower right of cell) the formulas give me an incorrect value. If I double click on that cell that was dragged down then press enter the formula works. Mysterious!

Is there a macro that can set my sheet back to normal or a setting that tells excel not to try and help? I'm using Excel 2010.

 

[AlphaFrog]

Just a shot in the dark. If you press the F9 key, do your copied formulas update?

If yes, then somehow your workbook Calculation Mode option got switched to Manual Calculation. I don't see how that could have happened from your macro code though.

You can reset it back to automatic calculation mode under the Excel Options menu or run a macro like this...

Sub Reset_AutoCalc()
Application.Calculation = xlAutomatic
End Sub