VBA Macro Changes formulas in worksheet and I don't know why!!

[osinrider04]

I've written a pretty basic macro that allows the user to locate a data file (.txt) saved on their computer and upload it to a data dump tab in an excel workbook. The macro then cleans the data from .txt into a text-to-column delimited layout, where each column has its own set of values instead of everything being crammed into column A (uses spaces as a way to indicate a new column). The macro is kicked off by selecting a button on the main page as well.

All of this works great except for one very odd thing that I just noticed. I have various other tabs that I am now pointing back to this data dump tab using sumifs, vlookups, etc--nothing crazy there. However, after I run the macro I noticed my results were all skewed. It turns out that the macro is actually CHANGING my formulas in the other sheets. I've never seen this happen before especially considering the macro literally has nothing to do with these formulas. The macro just creates the file, and these formulas reside in a different tab that reference the data.

Here is an example of the formula: =SUMIF(RawData!$C:$C,DTS!$A$4,RawData!$E:$E)
After running the macro is goes to this: =SUMIF(RawData!$D:$D,DTS!$A$4,RawData!$F:$F)

As you can see it is slightly changing my formula and I have no idea why!

Has anyone ever seen something like this happen before? I can't post the actual workbook online but here is the start of my code. I'm a noob at vba so don't hate too much.

Sub ImportData()

Application.ScreenUpdating = False

Sheets("RawData").Select
Cells.Select
Selection.ClearContents
Selection.ClearFormats

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Sheet As Worksheet
Dim PasteStart As Range

Set wb1 = ActiveWorkbook
Set PasteStart = [RawData!A1]

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a data extract")

If FileToOpen = False Then
MsgBox "No File Specified.", vbExclamation, "ERROR"
Exit Sub
Else
Set wb2 = Workbooks.Open(Filename:=FileToOpen)

For Each Sheet In wb2.Sheets
With Sheet.UsedRange
.Copy PasteStart
Set PasteStart = PasteStart.Offset(.Rows.Count)
End With
Next Sheet

End If

wb2.Close

' From here down it cleans and manipulates the data into columns and rows

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(28, 1), Array(33, 1), Array(56, 1), _
Array(75, 1), Array(95, 1), Array(115, 1), Array(135, 1)), TrailingMinusNumbers:= _
True

' Inserts a column after the Classification column and creates the 6 digit program code by using the left() formula

Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C4").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],6)"
Range("C4").Select
Selection.AutoFill Destination:=Range("C4:C50")
Range("C4:C50").Select

Range("C1").Value = "Program Code"


Application.ScreenUpdating = True

End Sub

 

[JoeMo]

Possibly your formulas are reacting to the column insertion performed by your macro.

Here:

Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

 

[osinrider04]

The reason I'm doing that is because I have a large code that I need to use for the reference in the sumif. For example, the code in the database pull is ABC12345. In order for the sumif to work for my needs, I only need the first part of the code, 'ABC'. It's the equivalent of, say, city and state and I need the city. So I'm using the left() function to grab the digits I need from the front of the code, then running function and auto-filling down 50 rows.

This could be done manually but I didn't see a reason why the macro couldn't handle it. Do you think this is the problem?

 

[JoeMo]

The reason I'm doing that is because I have a large code that I need to use for the reference in the sumif. For example, the code in the database pull is ABC12345. In order for the sumif to work for my needs, I only need the first part of the code, 'ABC'. It's the equivalent of, say, city and state and I need the city. So I'm using the left() function to grab the digits I need from the front of the code, then running function and auto-filling down 50 rows.

This could be done manually but I didn't see a reason why the macro couldn't handle it. Do you think this is the problem?

I don't understand what you are trying to do well enough to know. I'm simply pointing out that when you insert a column, whether manually or with VBA, you can expect some formulas will be changed. Wasn't that the question your OP posed?

 

[osinrider04]

I don't understand what you are trying to do well enough to know. I'm simply pointing out that when you insert a column, whether manually or with VBA, you can expect some formulas will be changed. Wasn't that the question your OP posed?

So I have a data dump tab, where the macro locates a file that was run from a database and plops it into this tab. However, the database is really old and the exported data is not clean, so this macro just organizes the data into columns to make it a typical matrix/table. The 'insert' function in the macro is because I need to create 1 new column that does not exist in the database. This created column is just using the left( ) function to strip out and leave me with the first 10 characters of the column in front of it, which is all I need to do my report.

For example, taking something like 40DHSA20160809, down to just 40DHSA. In this example, I don't need that last set of numbers which is a date or something.

The thing I'm not understanding is why me inserting a column in this data dump tab would be affecting a formula that is on a tab that the macro is not even affecting. The only link between the data tab and my other tab are standard excel formulas that reference the data tab. I've even tried protecting the workbook and creating absolute reference formulas with no luck, and the macro still finds a way to change the formulas.

Any ideas?

 

[JoeMo]

So I have a data dump tab, where the macro locates a file that was run from a database and plops it into this tab. However, the database is really old and the exported data is not clean, so this macro just organizes the data into columns to make it a typical matrix/table. The 'insert' function in the macro is because I need to create 1 new column that does not exist in the database. This created column is just using the left( ) function to strip out and leave me with the first 10 characters of the column in front of it, which is all I need to do my report.

For example, taking something like 40DHSA20160809, down to just 40DHSA. In this example, I don't need that last set of numbers which is a date or something.

The thing I'm not understanding is why me inserting a column in this data dump tab would be affecting a formula that is on a tab that the macro is not even affecting. The only link between the data tab and my other tab are standard excel formulas that reference the data tab. I've even tried protecting the workbook and creating absolute reference formulas with no luck, and the macro still finds a way to change the formulas.

Any ideas?

The bold line above is your answer. Here's a simple test that involves no macros to convince you. Open a new workbook and if necessary add a Sheet2 (this will be the "Data" sheet). On Sheet1 (any cell) insert this formula:
=SUM(Sheet2!$C1:$C4)
You now have a formula that references Sheet2 (think of Sheet2 as your Data tab).
Now go to Sheet2 and insert a column before Col C. Then return to Sheet1 and notice that the formula is now:
=SUM(Sheet2!$D1:$D4)
Excel has adjusted the formula so the correct range will still be summed. Of course, the same thing would happen if you inserted the column using a VBA command.

 

[osinrider04]

The bold line above is your answer. Here's a simple test that involves no macros to convince you. Open a new workbook and if necessary add a Sheet2 (this will be the "Data" sheet). On Sheet1 (any cell) insert this formula:
=SUM(Sheet2!$C1:$C4)
You now have a formula that references Sheet2 (think of Sheet2 as your Data tab).
Now go to Sheet2 and insert a column before Col C. Then return to Sheet1 and notice that the formula is now:
=SUM(Sheet2!$D1:$D4)
Excel has adjusted the formula so the correct range will still be summed. Of course, the same thing would happen if you inserted the column using a VBA command.

Very interesting. So I went back and updated my code to add the new column with the formula to the end of the data and it worked fine. So thank you very much for the fast help.

I'm curious if you know a way to work around this issue though. Because what if for instance I couldn't add the new column of data to the end of the dataset, or I just wanted it where I originally had it, is there a way to create formulas that wouldn't adjust due to the macro creating that new column? At this point my situation is solved, but in the event others read this in future and have a slightly different need it would be nice to know of an alternative solution.

 

[JoeMo]

Very interesting. So I went back and updated my code to add the new column with the formula to the end of the data and it worked fine. So thank you very much for the fast help.

I'm curious if you know a way to work around this issue though. Because what if for instance I couldn't add the new column of data to the end of the dataset, or I just wanted it where I originally had it, is there a way to create formulas that wouldn't adjust due to the macro creating that new column? At this point my situation is solved, but in the event others read this in future and have a slightly different need it would be nice to know of an alternative solution.

You are welcome. Excel's ability to adjust formulas to things like column and row insertions is one of its most essential features. I don't know nor would I advocate any way to circumvent it!