REPOST: Please help with Output - Going across instead of down

J

Jack

I posted this a few days ago and havent had any replies. I assume that its
because maybe I wasnt clear enough. Hopefully I can explain what I am needing
the output to be.
I am using the following code to export cell selection to a txt file. The
code works fine, if I only select one column. However I run into problems
when I select multiple columns. Instead of the output being a1,b1,c1 a2,b2,c2
a3,b3,c3. I need the output to be a1,a2,a3 b1,b2,b3,c1,c2,c3 etc. depending
on number of columns selected.
Here is code I am currently using.
Sub Export2Textfile()
Dim Textfile As Variant
Dim LastRow As Long
Dim XportArea As Range
Dim Cel As Range
Dim iFnum As Integer
'Select a textfile to save to
Textfile = Application.GetSaveAsFilename( _
InitialFileName:="text.txt", _
FileFilter:="Text files, *.txt)", _
Title:="Save textfile as:")
If Textfile = False Then Exit Sub
On Error Resume Next
'Select the cells to export:
Set XportArea = Application.InputBox( _
"Select the cells to export:", _
"Use your mouse:", Selection.Address, Type:=8)
If XportArea Is Nothing Then Exit Sub
'open / create the textfile
iFnum = FreeFile
Open CStr(Textfile) For Output As iFnum
'loop cells
For Each Cel In XportArea
'write to textfile
Print #iFnum, Cel.Text
Next
Close #iFnum
End Sub

I am thinking I will need to add another loop. I just dont know how to get
it to go down instead of across. Any help would be greatly appreciated
 
D

Dave Peterson

Instead of:

For Each Cel In XportArea
'write to textfile
Print #iFnum, Cel.Text
Next

You could loop through the columns, then the rows of each column.

dim iCol as long
dim iRow as long

for icol = 1 to selection.columns.count
for irow = 1 to selection.rows.count
print #ifnum, selection.cells(irow,icol).text
next irow
next icol
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top