Loop

C

callakp

New Member
Joined
Oct 31, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Dear Sir,

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 I14.

Range A6 to D14 has data only on Row 6, 10 & 14. and Range E6 to I14 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 I7 then E8 to I8 then E9 to I9 then A10 to I10, E11 to I11 then E12 to I12 then E13 to I13 then A14 to I14.

Sample file attached for your reference.

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

Please help


Data Sheet

Sample.xlsx
ABCDEFGHI
5Voucher No*Voucher Date (DD-MM-YYYY)*Voucher Type*Is Invoice*Sales / Purchase Ledger Name*Stock Item Name*Qty*Rate*Stock Amount
6Sales 00101-04-2021SalesYesSale 28%Mobile1015,0001,50,000
7Sale 28%Mobile510,00050,000
8Sale @ 18%TV230,00060,000
9Sale @ 18%Battery26,00012,000
10Sales 00201-04-2021SalesYesSale 28%Mobile515,00075,000
11Sale 28%Mobile310,00030,000
12Sale @ 18%TV130,00030,000
13Sale @ 18%Battery26,00012,000
14Sales 00301-04-2021SalesYesSale 28%Mobile515,00075,000
Data
Cell Formulas
RangeFormula
I6:I14I6=G6*H6
Cells with Data Validation
CellAllowCriteria
C6ListSales, Purchase
D6ListYes, No
C10ListSales, Purchase
D10ListYes, No
C14ListSales, Purchase
D14ListYes, No



Expected looping result sheet.

Sample.xlsx
B
1Sales 001
201-04-2021
3Sales
4Yes
5Sale 28%
6Mobile
710
815000
9150000
10Sale 28%
11Mobile
125
1310000
1450000
15Sale @ 18%
16TV
172
1830000
1960000
20Sale @ 18%
21Battery
222
236000
2412000
25Sales 002
2601-04-2021
27Sales
28Yes
29Sale 28%
30Mobile
315
3215000
3375000
34Sale 28%
35Mobile
363
3710000
3830000
39Sale @ 18%
40TV
411
4230000
4330000
44Sale @ 18%
45Battery
462
476000
4812000
49Sales 003
5001-04-2021
51Sales
52Yes
53Sale 28%
54Mobile
555
5615000
5775000
Expected Result
Cell Formulas
RangeFormula
B1B1=Data!A6
B2B2=Data!B6
B3B3=Data!C6
B4B4=Data!D6
B5B5=Data!E6
B6B6=Data!F6
B7B7=Data!G6
B8B8=Data!H6
B9B9=Data!I6
B10B10=Data!E7
B11B11=Data!F7
B12B12=Data!G7
B13B13=Data!H7
B14B14=Data!I7
B15B15=Data!E8
B16B16=Data!F8
B17B17=Data!G8
B18B18=Data!H8
B19B19=Data!I8
B20B20=Data!E9
B21B21=Data!F9
B22B22=Data!G9
B23B23=Data!H9
B24B24=Data!I9
B25B25=Data!A10
B26B26=Data!B10
B27B27=Data!C10
B28B28=Data!D10
B29B29=Data!E10
B30B30=Data!F10
B31B31=Data!G10
B32B32=Data!H10
B33B33=Data!I10
B34B34=Data!E11
B35B35=Data!F11
B36B36=Data!G11
B37B37=Data!H11
B38B38=Data!I11
B39B39=Data!E12
B40B40=Data!F12
B41B41=Data!G12
B42B42=Data!H12
B43B43=Data!I12
B44B44=Data!E13
B45B45=Data!F13
B46B46=Data!G13
B47B47=Data!H13
B48B48=Data!I13
B49B49=Data!A14
B50B50=Data!B14
B51B51=Data!C14
B52B52=Data!D14
B53B53=Data!E14
B54B54=Data!F14
B55B55=Data!G14
B56B56=Data!H14
B57B57=Data!I14
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Please note:
The data is copied over from worksheet DATA, row 6 onwards.
The data is paste into worksheet RESULT, column B.
For this to work, worksheet RESULT needs to have at least one cell with data!



VBA Code: 
Sub NumCols()

' Make sure all worksheets are visible
    Worksheets("Result").Visible = True
    Worksheets("Data").Visible = True

' Determine the last columns on worksheet DATA that contains information
    Worksheets("Data").Select
    Dim LastCol_Data As Long
    LastCol_Data = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    ' If you got other data on worksheet 'DATA', then this formula might not work.
    ' You can also hard-code the number of columns by using the code mentioned below:
    ' LastCol_Data = 9

' Determine the last row on worksheet DATA that contains information
    Dim LastRow_data As Long
    LastRow_data = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row



For RowNumber = 6 To LastRow_data
For Columnnumber = 1 To LastCol_Data

    ' Copy the next cell within your range on worksheet DATA
        Worksheets("Data").Select
        Worksheets("Data").Cells(RowNumber, Columnnumber).Select
        ActiveCell.Copy
       
    ' On worksheet RESULT, determine the last row that contains data
        Worksheets("Result").Select
        Dim LastRow_Result As Long
        LastRow_Result = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

    ' On worksheet RESULT, select the row below the last row with data.
        Worksheets("Result").Cells(LastRow_Result + 1, 2).Select

    ' On worksheet RESULT, paste the data into the first empty cell of column B.
    ' Note that for this to work at least one cell in column B needs to have data!
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Next
Next


End Sub
 
Upvote 0
How about this:

VBA Code: 
Sub callakp()
'
    Dim InitialArrayColumnCounter   As Long, InitialArrayRowCounter     As Long
    Dim LastRowOfData               As Long, StartRowOfData             As Long
    Dim ResultArraySlot             As Long
    Dim ResultColumn                As String
    Dim InitialArray                As Variant, ResultArray             As Variant
    Dim DestinationWS               As Worksheet, SourceWS              As Worksheet
'
    StartRowOfData = 6                                                          ' <--- Set this to the starting row of data
    ResultColumn = "B"                                                          ' <--- Set this to column that you want to display the results in
    Set DestinationWS = Sheets("Expected")                                      ' <--- Set this to sheet where results will be placed
    Set SourceWS = Sheets("Data")                                               ' <--- Set this to sheet where the data originates
'
    LastRowOfData = SourceWS.Range("I" & Rows.Count).End(xlUp).Row              ' Get last row number of data
    InitialArray = SourceWS.Range("A" & StartRowOfData & ":I" & LastRowOfData)  ' Save data into 2 dimensional 1 based array ... 9 wide
'
    ReDim ResultArray(1 To 100000)                                              ' Establish a large 1 dimensional 1 based array
'
    ResultArraySlot = 0
'
    For InitialArrayRowCounter = 1 To LastRowOfData - StartRowOfData + 1                                        ' Initialize Row Loop
        For InitialArrayColumnCounter = 1 To 9                                                                  '   Initialize Column loop
            If InitialArray(InitialArrayRowCounter, InitialArrayColumnCounter) <> vbNullString Then             '       If cell in array is not blank then ...
                ResultArraySlot = ResultArraySlot + 1                                                           '           Increment slot counter
                ResultArray(ResultArraySlot) = InitialArray(InitialArrayRowCounter, InitialArrayColumnCounter)  '           Save cell value to ResultArray
            End If
        Next                                                                                                    '   Loop back for next Column
    Next                                                                                                        ' Loop back for next Row
'
    ReDim Preserve ResultArray(1 To ResultArraySlot)                            ' resize the ResultArray down to the actual used size
'
    DestinationWS.Range(ResultColumn & "1:" & ResultColumn & ResultArraySlot) = Application.Transpose(ResultArray)  ' Dump ResultArray to result column
End Sub

Should be much faster.
 
Upvote 0
@petertenthije This should double or triple the speed of the version of code you submitted:

VBA Code: 
Sub NumColsV2()
'
'   Turn Settings off
      Application.ScreenUpdating = False                                    ' Turn Screen Updating off
         Application.Calculation = xlCalculationManual                      ' Turn AutoCalculation off
        Application.EnableEvents = False                                    ' Turn EnableEvents off
'
' Make sure worksheets to be used are visible
    Worksheets("Result").Visible = True
    Worksheets("Data").Visible = True
'
'   Determine the last columns on worksheet DATA that contains information
    Dim LastCol_Data As Long
    LastCol_Data = Worksheets("Data").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
'
'   If you got other data on worksheet 'DATA', then this formula might not work.
'   You can also hard-code the number of columns by using the code mentioned below:
'   LastCol_Data = 9
'
'   Determine the last row on worksheet DATA that contains information
    Dim LastRow_data As Long
    LastRow_data = Worksheets("Data").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
'
    Dim LastRow_Result As Long
'
    For RowNumber = 6 To LastRow_data
        For Columnnumber = 1 To LastCol_Data
'           Copy the next cell within your range on worksheet DATA
            Worksheets("Data").Cells(RowNumber, Columnnumber).Copy
'
'           On worksheet RESULT, determine the last row that contains data
            LastRow_Result = Worksheets("Result").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
'
'           On worksheet RESULT, select the row below the last row with data.
'           On worksheet RESULT, paste the data into the first empty cell of column B.
'           Note that for this to work at least one cell in column B needs to have data!
            Worksheets("Result").Cells(LastRow_Result + 1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Next
    Next
'
'   Turn Settings back on
    Application.EnableEvents = True                                                             ' Turn EnableEvents back on
    Application.Calculation = xlCalculationAutomatic                                            ' Turn AutoCalculation back on
    Application.ScreenUpdating = True                                                           ' Turn Screen Updating back on
End Sub

Removal of the multiple 'select's as well as the addition of turning off/on some settings were the changes I made. I also relocated the 'Dim' statement from within your loop.
If you are interested.
 
Upvote 0
johnnyL said:
Removal of the multiple 'select's as well as the addition of turning off/on some settings were the changes I made. I also relocated the 'Dim' statement from within your loop.
If you are interested.
Click to expand...
Thanks, it's always interesting to see what someone else would do to speed up macros!
 
Upvote 0
petertenthije said:
Please note:
The data is copied over from worksheet DATA, row 6 onwards.
The data is paste into worksheet RESULT, column B.
For this to work, worksheet RESULT needs to have at least one cell with data!



VBA Code: 
Sub NumCols()

' Make sure all worksheets are visible
    Worksheets("Result").Visible = True
    Worksheets("Data").Visible = True

' Determine the last columns on worksheet DATA that contains information
    Worksheets("Data").Select
    Dim LastCol_Data As Long
    LastCol_Data = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    ' If you got other data on worksheet 'DATA', then this formula might not work.
    ' You can also hard-code the number of columns by using the code mentioned below:
    ' LastCol_Data = 9

' Determine the last row on worksheet DATA that contains information
    Dim LastRow_data As Long
    LastRow_data = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row



For RowNumber = 6 To LastRow_data
For Columnnumber = 1 To LastCol_Data

    ' Copy the next cell within your range on worksheet DATA
        Worksheets("Data").Select
        Worksheets("Data").Cells(RowNumber, Columnnumber).Select
        ActiveCell.Copy
      
    ' On worksheet RESULT, determine the last row that contains data
        Worksheets("Result").Select
        Dim LastRow_Result As Long
        LastRow_Result = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

    ' On worksheet RESULT, select the row below the last row with data.
        Worksheets("Result").Cells(LastRow_Result + 1, 2).Select

    ' On worksheet RESULT, paste the data into the first empty cell of column B.
    ' Note that for this to work at least one cell in column B needs to have data!
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Next
Next


End Sub
Click to expand...
Thanks.
Actually I shown what i am expecting in the expected result but missed to mentioned that i need that to extract as xml.
and for each cell value in the xml need to be added with xml codes.
Your code works well to generate dynamic data, thanks. But one problem i felt as it need some data in the result sheet to start with and 2nd is it is repeating the data again and again if we run the code.
 
Upvote 0
johnnyL said:
@petertenthije This should double or triple the speed of the version of code you submitted:

VBA Code: 
Sub NumColsV2()
'
'   Turn Settings off
      Application.ScreenUpdating = False                                    ' Turn Screen Updating off
         Application.Calculation = xlCalculationManual                      ' Turn AutoCalculation off
        Application.EnableEvents = False                                    ' Turn EnableEvents off
'
' Make sure worksheets to be used are visible
    Worksheets("Result").Visible = True
    Worksheets("Data").Visible = True
'
'   Determine the last columns on worksheet DATA that contains information
    Dim LastCol_Data As Long
    LastCol_Data = Worksheets("Data").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
'
'   If you got other data on worksheet 'DATA', then this formula might not work.
'   You can also hard-code the number of columns by using the code mentioned below:
'   LastCol_Data = 9
'
'   Determine the last row on worksheet DATA that contains information
    Dim LastRow_data As Long
    LastRow_data = Worksheets("Data").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
'
    Dim LastRow_Result As Long
'
    For RowNumber = 6 To LastRow_data
        For Columnnumber = 1 To LastCol_Data
'           Copy the next cell within your range on worksheet DATA
            Worksheets("Data").Cells(RowNumber, Columnnumber).Copy
'
'           On worksheet RESULT, determine the last row that contains data
            LastRow_Result = Worksheets("Result").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
'
'           On worksheet RESULT, select the row below the last row with data.
'           On worksheet RESULT, paste the data into the first empty cell of column B.
'           Note that for this to work at least one cell in column B needs to have data!
            Worksheets("Result").Cells(LastRow_Result + 1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Next
    Next
'
'   Turn Settings back on
    Application.EnableEvents = True                                                             ' Turn EnableEvents back on
    Application.Calculation = xlCalculationAutomatic                                            ' Turn AutoCalculation back on
    Application.ScreenUpdating = True                                                           ' Turn Screen Updating back on
End Sub

Removal of the multiple 'select's as well as the addition of turning off/on some settings were the changes I made. I also relocated the 'Dim' statement from within your loop.
If you are interested.
Click to expand...
Dear Johnny,

Thanks for your guide.
I need for dynamic data and need to extract as xml file each cell value in the loop. I also need to add xml codes at the start and end of the looped cell value.
Kindly guide me.
most probable my data sheet will be like below mini sheet with rows may increase.

Sample.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
5Voucher No*Voucher Date (DD-MM-YYYY)*Voucher Type*Is Invoice*Sales / Purchase Ledger Name*Stock Item Name*Qty*Rate*Stock AmountParty Ledger*Auto PickAmountDiscount LedgerDr/ CrDiscount AmountGST Ledger 1GST RateDr/ CrGST 1 AmountGST Ledger 2GST RateDr/ CrGST 2 Amount
6Sales 00101-04-2021SalesYesSale 28%Mobile1015,0001,50,000Anil & Co DebtorDr335960DiscountDr5000CGST @ 14%14Cr21000SGST @ 14%14Cr21000
7Sale 28%Mobile510,00050,000CGST @ 14%14Cr7000SGST @ 14%14Cr7000
8Sale @ 18%TV230,00060,000CGST @9%9Cr5400CGST @9%9Cr5400
9Sale @ 18%Battery26,00012,000CGST @9%9Cr1080CGST @9%9Cr1080
10Sales 00201-04-2021SalesYesSale 28%Mobile515,00075,000Anil & Co DebtorDr183960CGST @ 14%14Cr10500SGST @ 14%14Cr10500
11Sale 28%Mobile310,00030,000CGST @ 14%14Cr4200SGST @ 14%14Cr4200
12Sale @ 18%TV130,00030,000CGST @9%9Cr2700CGST @9%9Cr2700
13Sale @ 18%Battery26,00012,000CGST @9%9Cr1080CGST @9%9Cr1080
14Sales 00301-04-2021SalesYesSale 28%Mobile515,00075,000Anil & Co DebtorDr96000CGST @ 14%14Cr10500SGST @ 14%14Cr10500
Data
Cell Formulas
RangeFormula
I6:I14I6=G6*H6
Cells with Data Validation
CellAllowCriteria
C6ListSales, Purchase
D6ListYes, No
C10ListSales, Purchase
D10ListYes, No
C14ListSales, Purchase
D14ListYes, No



And I will be expecting the result in xml file with for each cell there will be different xml codes at the start and end of cell values of the loop.

Sample.xlsm
B
1Sales 001
201-04-2021
3Sales
4Yes
5Sale 28%
6Mobile
710
815000
9150000
10Anil & Co Debtor
11Dr
12335960
13Discount
14Dr
155000
16CGST @ 14%
1714
18Cr
1921000
20SGST @ 14%
2114
22Cr
2321000
24Sale 28%
25Mobile
265
2710000
2850000
29CGST @ 14%
3014
31Cr
327000
33SGST @ 14%
3414
35Cr
367000
37Sale @ 18%
38TV
392
4030000
4160000
42CGST @9%
439
44Cr
455400
46CGST @9%
479
48Cr
495400
50Sale @ 18%
51Battery
522
536000
5412000
55CGST @9%
569
57Cr
581080
59CGST @9%
609
61Cr
621080
63Sales 002
6401-04-2021
65Sales
66Yes
67Sale 28%
68Mobile
695
7015000
7175000
72Anil & Co Debtor
73Dr
74183960
75CGST @ 14%
7614
77Cr
7810500
79SGST @ 14%
8014
81Cr
8210500
83Sale 28%
84Mobile
853
8610000
8730000
88CGST @ 14%
8914
90Cr
914200
92SGST @ 14%
9314
94Cr
954200
96Sale @ 18%
97TV
981
9930000
10030000
101CGST @9%
1029
103Cr
1042700
105CGST @9%
1069
107Cr
1082700
109Sale @ 18%
110Battery
1112
1126000
11312000
114CGST @9%
1159
116Cr
1171080
118CGST @9%
1199
120Cr
1211080
122Sales 003
12301-04-2021
124Sales
125Yes
126Sale 28%
127Mobile
1285
12915000
13075000
131Anil & Co Debtor
132Dr
13396000
134CGST @ 14%
13514
136Cr
13710500
138SGST @ 14%
13914
140Cr
14110500
Result
 
Upvote 0
@callakp please explain how post #3 in this thread did not answer your original question.
 
Upvote 0
johnnyL said:
@callakp please explain how post #3 in this thread did not answer your original question.
Click to expand...
Dear Johnny,

It works. But the result what I am expecting I am not able to communicate properly.
Sorry for the inconvenience.
The expected result sheet is just for reference. I don't need the looping data 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 XMLStr = XMLStr &"<Date>" & cells(r,c).value & "</Date>". and Print #fnum, XMLStr

Thanks a lot for your guide. It will be a great help and learning for me.
 
Upvote 0
petertenthije said:
Please note:
The data is copied over from worksheet DATA, row 6 onwards.
The data is paste into worksheet RESULT, column B.
For this to work, worksheet RESULT needs to have at least one cell with data!



VBA Code: 
Sub NumCols()

' Make sure all worksheets are visible
    Worksheets("Result").Visible = True
    Worksheets("Data").Visible = True

' Determine the last columns on worksheet DATA that contains information
    Worksheets("Data").Select
    Dim LastCol_Data As Long
    LastCol_Data = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    ' If you got other data on worksheet 'DATA', then this formula might not work.
    ' You can also hard-code the number of columns by using the code mentioned below:
    ' LastCol_Data = 9

' Determine the last row on worksheet DATA that contains information
    Dim LastRow_data As Long
    LastRow_data = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row



For RowNumber = 6 To LastRow_data
For Columnnumber = 1 To LastCol_Data

    ' Copy the next cell within your range on worksheet DATA
        Worksheets("Data").Select
        Worksheets("Data").Cells(RowNumber, Columnnumber).Select
        ActiveCell.Copy
      
    ' On worksheet RESULT, determine the last row that contains data
        Worksheets("Result").Select
        Dim LastRow_Result As Long
        LastRow_Result = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

    ' On worksheet RESULT, select the row below the last row with data.
        Worksheets("Result").Cells(LastRow_Result + 1, 2).Select

    ' On worksheet RESULT, paste the data into the first empty cell of column B.
    ' Note that for this to work at least one cell in column B needs to have data!
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Next
Next


End Sub
Click to expand...
Dear Peter,

Thanks for your guide.
I need for dynamic data and need to extract as xml file each cell value in the loop. I also need to add xml codes at the start and end of the looped cell value.
Kindly guide me.
most probable my data sheet will be like below mini sheet with rows may increase.

Data
ABCDEFGHIJKLMNOPQRSTUVW
Sample.xlsm
5Voucher No*Voucher Date (DD-MM-YYYY)*Voucher Type*Is Invoice*Sales / Purchase Ledger Name*Stock Item Name*Qty*Rate*Stock AmountParty Ledger*Auto PickAmountDiscount LedgerDr/ CrDiscount AmountGST Ledger 1GST RateDr/ CrGST 1 AmountGST Ledger 2GST RateDr/ CrGST 2 Amount
6Sales 00101-04-2021SalesYesSale 28%Mobile1015,0001,50,000Anil & Co DebtorDr335960DiscountDr5000CGST @ 14%14Cr21000SGST @ 14%14Cr21000
7Sale 28%Mobile510,00050,000CGST @ 14%14Cr7000SGST @ 14%14Cr7000
8Sale @ 18%TV230,00060,000CGST @9%9Cr5400CGST @9%9Cr5400
9Sale @ 18%Battery26,00012,000CGST @9%9Cr1080CGST @9%9Cr1080
10Sales 00201-04-2021SalesYesSale 28%Mobile515,00075,000Anil & Co DebtorDr183960CGST @ 14%14Cr10500SGST @ 14%14Cr10500
11Sale 28%Mobile310,00030,000CGST @ 14%14Cr4200SGST @ 14%14Cr4200
12Sale @ 18%TV130,00030,000CGST @9%9Cr2700CGST @9%9Cr2700
13Sale @ 18%Battery26,00012,000CGST @9%9Cr1080CGST @9%9Cr1080
14Sales 00301-04-2021SalesYesSale 28%Mobile515,00075,000Anil & Co DebtorDr96000CGST @ 14%14Cr10500SGST @ 14%14Cr10500
RangeFormula
Cell Formulas
I6:I14I6=G6*H6
CellAllowCriteria
Cells with Data Validation
C6ListSales, Purchase
D6ListYes, No
C10ListSales, Purchase
D10ListYes, No
C14ListSales, Purchase
D14ListYes, No



And I will be expecting the result in xml file with for each cell there will be different xml codes at the start and end of cell values of the loop.

Result
B
Sample.xlsm
1Sales 001
201-04-2021
3Sales
4Yes
5Sale 28%
6Mobile
710
815000
9150000
10Anil & Co Debtor
11Dr
12335960
13Discount
14Dr
155000
16CGST @ 14%
1714
18Cr
1921000
20SGST @ 14%
2114
22Cr
2321000
24Sale 28%
25Mobile
265
2710000
2850000
29CGST @ 14%
3014
31Cr
327000
33SGST @ 14%
3414
35Cr
367000
37Sale @ 18%
38TV
392
4030000
4160000
42CGST @9%
439
44Cr
455400
46CGST @9%
479
48Cr
495400
50Sale @ 18%
51Battery
522
536000
5412000
55CGST @9%
569
57Cr
581080
59CGST @9%
609
61Cr
621080
63Sales 002
6401-04-2021
65Sales
66Yes
67Sale 28%
68Mobile
695
7015000
7175000
72Anil & Co Debtor
73Dr
74183960
75CGST @ 14%
7614
77Cr
7810500
79SGST @ 14%
8014
81Cr
8210500
83Sale 28%
84Mobile
853
8610000
8730000
88CGST @ 14%
8914
90Cr
914200
92SGST @ 14%
9314
94Cr
954200
96Sale @ 18%
97TV
981
9930000
10030000
101CGST @9%
1029
103Cr
1042700
105CGST @9%
1069
107Cr
1082700
109Sale @ 18%
110Battery
1112
1126000
11312000
114CGST @9%
1159
116Cr
1171080
118CGST @9%
1199
120Cr
1211080
122Sales 003
12301-04-2021
124Sales
125Yes
126Sale 28%
127Mobile
1285
12915000
13075000
131Anil & Co Debtor
132Dr
13396000
134CGST @ 14%
13514
136Cr
13710500
138SGST @ 14%
13914
140Cr
14110500
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
VBA Loop Through Named Ranges
Replies
1
Views
139
Jeffrey Mahoney
Jeffrey Mahoney
E
  • Question
Formula to look at Column G&H in groups of data & returns the number repeated the most in each group.
Replies
4
Views
110
Event2020
E
S
  • Question
VBA to loop through sheets and copy to new workbook including the pivot tab
Replies
0
Views
196
simont1975
S
K
  • Question
How to find stock data that Excel can't match to it's data source provider?
Replies
0
Views
58
kcmuppet
K
H
  • Question
'How to' question about a Dashboard
Replies
5
Views
216
etaf
etaf

Forum statistics

Threads
1,215,059
Messages
6,122,918
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top