Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Looping code needed

0

Dear VBA Exports,

I need your guidance to build a vba sript to loop through few data.

I can loop with in a range where all the row and column has data.

But when there are few column of the row doesnot have data i am not able to build a loop sript.

For example

I have a range from A6 to  W14.  and range can be dynamic.

Range A6 to D14 has data only on Row 6, 10 & 14. and Range E6 to W14 has data in all cells.

I am looking for a loop to get result in the below format (Seperate line for each cell data"

Basically I am looking for it to loop from A6 to I14 then E7 to W7 then E8 to W8 then E9 to W9 then A10 to W10, E11 to W11 then E12 to W12 then E13 to W13 then A14 to W14.

Sample file attached for your reference.

Below is the result what i am looking to get through the loop. 

Please help

Expected Result in xml or txt file with each line separately.

I have to add some addtional xml code before and after each cell value.

The expected result sheet is just for reference. I don't need it in another sheet. I need the looping data to be saved as xml file. and I have to add different xml lines such as say "<Date>"& cells(#,#).value &"</Date>" like this to each looped value.

You may presume the Row 5 heading as xml start and end code. Please guide me with the vba code.

If you can help me with dynamic row it will be great as i can use the cells(r,c).value with the xml codes.

Here r dim as row and c dim as column.

so my code can go as "<Date>" & cells(r,c).value & "</Date>". 

Please provide me a working code. Thanks in advance.

Answer
Discuss

Answers

0

Hi and welcome to the Forum.

The attached revised file has a button "List values only"" which triggers this sub:

Sub ListRangeValues()

Dim Cl As Range, n As Integer



n = 0

For Each Cl In Worksheets("Data").Range("A6:I14")

    If Cl.Value <> "" Then

        n = n + 1

        Worksheets("Result").Cells(n, 2) = Cl.Value

        Worksheets("Result").Cells(n, 1) = Cl.Address

    End If

Next Cl

MsgBox n & " values found"



End Sub
and adds the cell values to column B of new sheet "Results" plus addresses in column A.

In response to your revised question (way too long!), the code below is in the second file (... text file.xlsm) and uses an array to list the sequence of ranges you want to loop through. I've removed the cell address from the output in sheet "Result" and saved that sheet as a text file. The code is more complex (e.g. it calls and sends each range to a second sub ScanThenPrint to output values from each range) and is commented to help you:

Option Explicit

Public n As Long, Cl As Range

Sub ListRangeValues2()

Dim m As Long, myLoop As Range
Dim LoopsArray As Variant, TxtSv As Variant ' variables for ranges and text file

' clear output
Worksheets("Result").Range("B:B").Clear
'create an array of range strings in order
LoopsArray = Array("A6:I14", "E7:W7", "E8:W8", "E9:W9", "A10:W10", "E11:W11", "E12:W12", "E13:W13", "A14:W14")
n = 0
'Loop through the array...
For m = LBound(LoopsArray) To UBound(LoopsArray)
    Set myLoop = Worksheets("Data").Range(LoopsArray(m))
    '... and call the function to output values in the range
    Call ScanThenPrint(myLoop)
Next m

TxtSv = Application.GetSaveAsFilename("ResultsAsText", "TXT File (*.txt), *.txt")
If TxtSv = False Then Exit Sub
Worksheets("Result").Copy
ActiveWorkbook.SaveAs TxtSv, xlUnicodeText
If ActiveWorkbook.Name <> ThisWorkbook.Name Then
    ActiveWorkbook.Close False
End If

MsgBox n & " values found and saved in file " & TxtSv

End Sub

Sub ScanThenPrint(Rng As Range)

For Each Cl In Rng
    If Cl.Value <> "" Then
        n = n + 1
        Worksheets("Result").Cells(n, 2) = Cl.Value
    End If
Next Cl

End Sub

Hope this helps.

Discuss

Discussion

p.s I guess Autocorrect led to your amusing intro "Dear VBA Exports
John_Ru (rep: 6102) Oct 31, '21 at 4:13 pm
Dear John,

Thanks.
I need to extract it as xml or txt with each line.
Posting the complete data what i actully need.
Please help.



Expected result in xml or text file
callakp Nov 1, '21 at 9:42 am
Anil(?), your question has become ludicrously long and detailed but hopefully my revised answer works for you.
John_Ru (rep: 6102) Nov 1, '21 at 1:07 pm
Dear John, Thanks for the guide. Your code works to provide me result in another sheet. The expected result sheet is just for reference. I don't need it in another sheet. I need the looping data to be saved as xml file. and I have to add different xml lines such as say "<Date>"&cells(#,#).value&"</Date>" like this to each looped value.
You may presume the Row 5 heading as xml start and end code. Can you please guide me with the vba code please. If you can help me with dynamic row it will be great as i can use the cells(r,c).value with the xml codes.
Here r dim as row and c dim as column.
so my code can go as "<Date>" & cells(r,c).value & "</Date>". Sorry for miscommunication. 
Please guide me.
callakp Nov 2, '21 at 2:23 am
I see you have reduced/ revised your question (thanks) but did the revised code output the correct sequence of text?

Can't look at your file yet but if the same xml text is around each cell value, don't you know how to do that? 
John_Ru (rep: 6102) Nov 2, '21 at 2:31 am
Dear John,

I am very new to vba. Just trying to learn. The revised code is generating text file from the sheet result and sequence is also fine but I am not going to create a result sheet. The xml should be created from data sheet. To create it directly as xml or text please guide with the code.

Where my requirement is to get the cell value and add xml codes as i emplained.

If you can help me with dynamic row it will be great as i can use the cells(r,c).value with the xml codes. Here r dim as row and c dim as column. So my code can go as XMLStr = XMLStr &"<Date>" & cells(r,c).value & "</Date>". and Print #fnum, XMLStr.

Sorry I am disturbing you. I am very poor in vba. not aware how to link looping cell value with xml codes unless i get the dynamic r & c (Row and column)  When i get the dyanmic r & c,

I used to use the below code. Beacuse each cell value wil have different Start and end code of xml.
XMLStr &"<Date>" & cells(r,c).value & "</Date>". 

Request you to please help me.
callakp Nov 2, '21 at 3:23 am
Anil(?), this is a Q&A forum but you original question has mutated and increased in effort needed (which is frowned upon- we prefer well-defined questions not project requirements!).

I'm no expert in XML (so unclear what codes shoud surround the values) and don't have much time today (remember that I'm just a volunteer contributor here) so probably won't do any more on this  question.

If you revise your file to show the expected  results as <<xml codes>> <<cell value in bold>> <<xml codes>> it might be clearer for me and others
John_Ru (rep: 6102) Nov 2, '21 at 7:56 am
Dear John,

Thanks.
If you can guide the same with dim c and r as column and row and loop with FOR and Next and do until or with IF statements it will be helpful.
Rest hopefully I can take care.

What i need is in the loop result i should get cells(r,c).value

Or if you can help me to generate dynamic ranges for each Row for which Column has data till the next row with the data. for example from A6 to w8, A9 to w12 & A13 to w14 in my case. Then i will try to loop with in that range and then next range etc.

I am more comfortable with For Next, If statements, do until to understand the vba.
Please guide me with these.

If possbile just guide me whenever you get time. Thanks a lot.
Sorry if i am disturbing you. You may excuse me. Have a good day.
Regards,
Anil
callakp Nov 2, '21 at 8:27 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login