Split results of query array, apply different formulas to each then send to Worksheet?

[Event2020]

I have a macro that uses a query to open a tab delimited text file and import some (but not all) of the columns into a specific worksheet in the same workbook from where the macro was called. This works perfectly except I need to run the data from each column through a formula that cleans up the data and formats it according to my needs and then writes that to the work sheet.

In the code below you can see within the array statement the columns I am importing from the text file so is there any way to split the array, send each column to its own unique formula and then write the result to the worksheet specified in my code?

I have searched online but I can not find any reference to doing this.

Sub importText()
    Dim FName
    Dim rDest As Range
    Dim Answer As VbMsgBoxResult
    Dim fDialog As FileDialog, result As Integer
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    
'Optional: FileDialog properties
    fDialog.AllowMultiSelect = False
    fDialog.Title = "Select a file"
    fDialog.InitialFileName = "F:\"
    
'Optional: Add filters
    fDialog.Filters.Clear
    fDialog.Filters.Add "Text/CSV files (*.txt; *.csv)", "*.txt; *.csv", 1
    
    Answer = MsgBox("Are You Sure You Want To Import A Text File?", vbYesNo + vbCritical, "Import A Text File")
    If Answer = vbYes Then
        Application.ScreenUpdating = False
    Else
        Exit Sub
    End If
    
    FName = Application.GetOpenFilename(filefilter:="Text/CSV files (*.txt; *.csv),*.txt;*.csv", MultiSelect:=False)
    If FName = False Then Exit Sub
    
    Set rDest = Worksheets("Tracks").Cells(1, 3)
  
    With rDest.Worksheet.QueryTables.Add(Connection:= _
        "TEXT;" & FName, Destination:=rDest)
        .Name = "new 1_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .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 = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 9, 9, 9, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 1, _
        1, 1, 1, 9)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[alansidman]

Using XL2BB, Upload some same data and then upload a mocked up version of your solution. The solution may be easier to solve than trying to re-engineer non working code.

 

[Event2020]

Using XL2BB, Upload some same data and then upload a mocked up version of your solution. The solution may be easier to solve than trying to re-engineer non working code.

Your comment is unhelpful and I do not understand what you mean by non working code, my code above works perfectly and I am asking if it is possible to add formulas to each column that the array represents before that column is then inserted in the destination worksheet.

If I had a solution to mock up then I would have no need to seek suggestions in this forum?

 

[alansidman]

When I ask you to mock up a solution, I don't expect you to write code. I want to see visually what you have as a before and after scenario. of your workbook. I would prefer to write my own code/solution rather than try to edit and "fix" and understand someone elses. If you do not wish to share a workbook before and after scenario so that we can understand your needs and expectations, then ok. I will move on to helping someone else and leave this for another. Good Luck with your project.

 

[Event2020]

Your are continuing to be unhelpful and presumptuous. You presumed that I knew what XL2BB was and how to use it (I did not) and you presume that I do not wish to share a workbook which I have not said. I think it is best that you "move on to help someone else" as I do not care for your attitude or presumptions when it was you who did not fully explain yourself. Good day.