Find Loop Not working correctly

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 4.75 out of 5)
Go back to All Questions Login or Register

I am trying to design a macro to automate importing a CSV file. I currently put the filename without the extension in column A.

Next the macro lets you choose the file first, loops through column A and finds that file name without the .csv extension. I want to offset by one column (to column B) and import the information starting there.

The macro runs, however, column A is is moved to column U and the import happens at A1? Just can’t seem to figure out what I am doing wrong, but I have given it a shot.

I Believe I am messing up the Loop or Find, because taking out the csv input it select Column A and that is it. So it is not finding my Filename (with or without extension).

Any help would be most appreciated.

Sub CSVauto()
' CSVauto Macro
' Keyboard Shortcut: Option+Cmd+x
' Declaring and setting variables for choosing CSV to import
Dim csvFileName As Variant

'Prompt window to choose csv file
csvFileName = Application.GetOpenFilename(FileFilter:="")
If csvFileName = False Then Exit Sub

'Setting a variable to find Experimental form name in Data Summary
Dim whatToFind As String

'Declaring that variable
csvNoExt = Replace(csvFileName, ".csv", "")
whatToFind = csvNoExt

'Looping through A column to find csvFileName without .csv extension
Set cell = Selection.Find(What:=whatToFind, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

'Selecting cell in column B for csv input
If Not cell Is Nothing Then
End If

Dim newRange As Range
Set newRange = Range(ActiveCell, ActiveCell.Offset(0, 1))

'Formatting for CSV and input
With newRange.Parent.QueryTables.Add(Connection:="TEXT;" & csvFileName, Destination:=newRange)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlMacintosh
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
.UseListObject = False
End With

'Formatting DataSummary sheet to fit "requirements" :)
Cells.Replace What:=">=", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
Cells.Replace What:="C121", Replacement:="C2", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Cells.Replace What:="P1211", Replacement:="P21", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False

End Sub

Simply the best place to learn VBA!

Error: Please enter a valid email address

Error: Invalid email

Error: Please enter your first name

Error: Please enter your last name

Error: Please enter a username

Error: Please enter a password

Error: Please confirm your password

Error: Password and password confirmation do not match