osinrider04
New Member
- Joined
- Jan 7, 2015
- Messages
- 9
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
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
Last edited: