Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
looping through rows and columns | Excel Discussion (Misc queries) | |||
looping across columns in range? | Excel Discussion (Misc queries) | |||
Looping thru columns | Excel Programming | |||
Looping through columns | Excel Programming | |||
Looping thru columns beyond Z | Excel Programming |