enter image description hereI'm trying to compare two variables within a macro but those two variables are going to be changing. Here's what I have so far:
Selection.End(xlToRight).Select
Dim Year As Integer
Year = ActiveCell.Value
ActiveCell.Offset(-1, 0).Select
Dim Year2 As Integer
Year 2 = ActiveCell.Value
If Year2 <> Year + 1 Then
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End If
Okay so basically this is what I'm trying to do. I asked a question already about the first part so sorry for repeating myself but this may lend clarity to this question. Essentially, I have to create a macro that allows the user to choose a file and have it uploaded and appended to the end of a worksheet. However, the data files go in sequential order named "2015 latest.txt", "2016 latest.txt", "2017 latest.txt", etc. I need the macro to stop the data from being appended if the user uploads a file out of sequence. So if the last file uploaded was "2016 latest.txt" and they choose "2018 latest.txt" I need the data to be deleted or the file to be blocked from importing all together. I couldn't figure out how to manipulate the macro as to the name of the file (I tried using a static variable but that didn't work). Instead I just went with the data (since it lists the year) and tried naming two variables and comparing them and then deleting the data if it doesn't match. Here's my full macro:
Option Explicit
Sub ImportTextFile()
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Dim fName As String, LastRow As Long
fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If fName = "False" Then Exit Sub
LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, _
Destination:=Range("A" & LastRow))
.Name = "sample"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
Selection.EntireRow.Delete
Selection.End(xlToRight).Select
Dim Year As Integer
Year = ActiveCell.Value
ActiveCell.Offset(-1, 0).Select
Dim Year2 As Integer
Year2 = ActiveCell.Value
If Year2 <> Year + 1 Then
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End If
End Sub
