issues after text import

[rollingzep]

Hi,

<p>

I am able to import CSV files into 3 worksheets of a workbook without issues.
But when I tried to add code to make changes, It did not take the change.
I suspect the worksheet is not Active. So I tried to again set the workbook and worksheet
But it does not chnage the format or name the column BB as Date or make the other changes
</p>
<code>
Sub TSS()

Dim wbexcel As Workbook
Dim ws As Worksheet
Dim intTextFile As Integer
Dim strFileName As String
Dim LastRow As Long
Dim i As Long


Set wbexcel = ThisWorkbook
Set ws = wbexcel.Sheets("TSS Trans")
ws.Cells.ClearContents

'Import TSS Trans CSV
strFileName = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Provide Text or CSV File:")

With ws.QueryTables.Add(Connection:="TEXT;" & strFileName, Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh

End With

MsgBox ("TSS Trans imported")

Set wbexcel = ActiveWorkbook
Set ws = wbexcel.Sheets("TSS Trans")

'change the format to text
ws.Columns("AG:AG").NumberFormat = "@"

'Add Date column

ws.Range("BB1").Name = "Date"

LastRow = Range("AY" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Range("AY" & i).Value = " " Or Range("AY" & i).Value = Empty Then
Range("BB" & i).Value = "1"
End If
Next i
End Sub
</code>

 

[Candyman8019]

Do you know what line it is failing on? Or do you get an error message?

 

[rollingzep]

I was able to resolve it by including the worksheet reference as in ws.Range rather than just If Range("AY" & i).Value = " " Or Range("AY" & i).Value = Empty Then

LastRow = ws.Range("AY" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If ws.Range("AY" & i).Value = " " Or ws.Range("AY" & i).Value = Empty Then
ws.Range("BB" & i).Value = "1"
End If
Next i