Looping through Columns then Rows
 

Hi Guys - I'm struggling with this. I have a spreadsheet (CSV file)
generated from a VBA module in MSP which extracts specific data. The
output file that I'm opening in Excel depends upon the MSP project that
I run the other code against so, I don't know how many colunms or rows
will be in use. What I need to do is for each row used, loop through
each column used in that row and concatenate required cells values into
a temporary string that I can output to a file. Then clear this string
and move on to the next row. I can't seem to figure out the correct
sequence of the counters and where to clear dowm the temp string value.

Any ideas gratefully received

Best Rgds

Looping through Columns then Rows
 

Hello JCP,

Here is macro example you can use or modify to copy the CSV data into a
text file.


Code:
--------------------
Sub SaveCSV()

Dim ColMax As Long
Dim Data
Dim DataFile As String
Dim FF As Integer
Dim FirstRow As Long
Dim I As Long
Dim J As Long
Dim LastCol As Long
Dim LastRow As Long

FF = FreeFile
DataFile = "C:\CSVData.txt"

With ActiveSheet.UsedRange
FirstRow = .Row
LastRow = .Rows.Count + X + 1
End With

ColMax = ActiveSheet.Columns.Count

Open DataFile For Output As #FF
For I = FirstRow To LastRow
Data = ""
LastCol = Cells(I, ColMax).End(xlToLeft).Column
For J = 1 To LastCol
Data = Data & Cells(I, J).Value & ","
Next J
Data = Left(Data, Len(Data) - 1)
Write #FF, Data
Next I
Close #FF

End Sub

--------------------


--
Leith Ross


------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=480356

Looping through Columns then Rows
 

Hi Leith

Thanks so much for your reply. Sorry for being slightly obtuse, but if I
wanted to test the content of the cells for mid(<cell,1)="c" where
would I insert this ?- tried a couple of places including here...

For J = 1 To LastCol
If Mid(ActiveSheet.Cells(I, J).Value, 1) = "c" Then
Data = Data & Cells(I, J).Value & ","
End if

But it fails on
Data = Left(Data, Len(Data) - 1)

with an Invalid proc call or arguement, thanks for your help

Best Rgds

*** Sent via Developersdex http://www.developersdex.com ***

Looping through Columns then Rows
 

Hello John Paul,

From your last post it looks like you want to test the cell first
before concatenating the contents. The function of Data = Left(Data,
Len(Data) - 1 is to remove the final comma from the concatenated string
before it is written into the file.

Here is code with the corrections ...


Code:
--------------------
Sub SaveCSV()

Dim ColMax As Long
Dim Data
Dim DataFile As String
Dim FF As Integer
Dim FirstRow As Long
Dim I As Long
Dim J As Long
Dim LastCol As Long
Dim LastRow As Long

FF = FreeFile
DataFile = "C:\CSVData.txt"

With ActiveSheet.UsedRange
FirstRow = .Row
LastRow = .Rows.Count + X + 1
End With

ColMax = ActiveSheet.Columns.Count

Open DataFile For Output As #FF
For I = FirstRow To LastRow
Data = ""
LastCol = Cells(I, ColMax).End(xlToLeft).Column
For J = 1 To LastCol
If Mid(Cells(I, J).Value, 1) = "c" Then
Data = Data & Cells(I, J).Value & ","
End If
Next J
If Data < "" Then
Data = Left(Data, Len(Data) - 1)
Write #FF, Data
End If
Next I
Close #FF

End Sub

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=480356