Looping in Excel

[lespaul]

I have a fixed width ascii text file that I am opening in Excel using a macro in another file. I am only selecting one of the fields from the text file. So I have my excel file with the macro (venire) that opens a text file which makes a new spreadsheet (bern). Now bern has a list in column A of names in Last Name, First Name format. I have a loop that should look at each person in the column and change the name to First name Last Name. It works for the first person, but never continues in the loop. What am I doing wrong?!!

Thanks for any help.

leslie

Sub Autpen()
'
' Macro1 Macro
' Macro recorded 4/11/2002 by landrews
'

'
Dim strCurrentName As String
Dim strName As String
Dim intCurrentColumn As Integer
Dim intCurrentRow As Integer


Workbooks.OpenText Filename:= _
"C:\Documents and Settings\landrews\My Documents\bern.bin", Origin:=_
xlWindows, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, _
9), Array(26, 9), Array(62, 9), Array(108, 9), Array(118, 9),
Array(120, 9), Array(126, 9), _
Array(132, 9), Array(135, 9), Array(143, 9), Array(150, 9),
Array(159, 1), Array(189, 9), _
Array(211, 9), Array(226, 9), Array(237, 9), Array(245, 9),
Array(248, 9), Array(258, 9), _
Array(266, 9))

intCurrentColumn = 1
intCurrentRow = 1
strCurrentName = Worksheets("bern&quot.Cells intCurrentColumn,
intCurrentRow).Value

Do While Trim(strCurrentName) <> &quot;&quot;
strName = strCurrentName
strCurrentName = Trim(Mid(strName, InStr(strName, &quot;,&quot + 1)) & &quot; &quot; &
Trim(Mid(strName, 1, InStr(strName, &quot;,&quot - 1))
Worksheets(&quot;bern&quot.Cells(intCurrentColumn, intCurrentRow).Value =
strCurrentName
'Go to next row
intCurrentRow = intCurrentRow + 1
Worksheets(&quot;bern&quot.Cells(intCurrentColumn, intCurrentRow).Select
strCurrentName = Worksheets(&quot;bern&quot.Cells(intCurrentColumn,
intCurrentRow).Value
Loop


End Sub

 

[RobBroekhuis]

I believe the cells method needs the row,column instead of column,row as arguments. Have you tried using debug mode to see what your selection is doing?
You can write the code much simpler. Try the following:

dim cell as range
...
set cell=Worksheets("bern").range("A1")
Do While Trim(cell) <> &quot;&quot;
   cell=Trim(Mid(cell, InStr(cell, &quot;,&quot;) + 1)) & &quot; &quot; & _
        Trim(Mid(cell, 1, InStr(cell, &quot;,&quot;) - 1))
   set cell=cell.offset(1,0)
loop

Rob

 

[lespaul]

Thanks!! worked perfectly!!

Now how can I save the txt file as a spreadsheet file. I've tried using the saveas method, but it's not working.

I want to save the file as veniretmp as a normal excel file and I keep getting syntax errors!

thanks!

leslie

 

[RobBroekhuis]

try

activeworkbook.SaveAs &quot;veniretmp.xls&quot;,xlWorkbookNormal

Rob