Hi all,Good day to you
can anyone help me with a concatenate macro program for the attached sheet.
i add tried with recordings but result varies from cell to cell.
I Uploaded my sample attachment sheet.
Thanks in advance
i add tried with recordings but result varies from cell to cell.
sub addAndMerge(event)
oSheet = thisComponent.Sheets(0)
oSheet.Columns.insertbyindex(7,2)
oSheet.getCellRangeByName("H1").String = " TOTAL QTY"
oSheet.getCellRangeByName("I1").String = " SL NO"
doc = thiscomponent
sheet = doc.CurrentController.ActiveSheet
sum = 0
row = 1
first = row
counter = 1
do
range = sheet.getCellRangeByPosition(3, row, 8, row+1)
data = range.DataArray
sum = sum & data(0)(3)
if data(0)(0) <> data(1)(0) then
sheet.getCellByPosition(7, first).setValue(sum)
tobemerged = sheet.getCellRangeByPosition(7, first, 7, row)
tobemerged.merge(True)
sheet.getCellByPosition(8, first).setValue(counter)
tobemerged = sheet.getCellRangeByPosition(8, first, 8, row)
tobemerged.merge(True)
sheet.Columns.removeByIndex(16, 1)
sum = 0
first = row + 1
counter = counter +1
end if
row = row +1
loop until data(1)(0) = ""
end sub
Users browsing this forum: No registered users and 6 guests