OpenOffice.info

Code: Alles auswählen

function rverketten(range() as variant )as string

if isarray(range()) then

	z = ubound(range,1)
	s = ubound(range,2)
	dim outlist(z*s)
	for i = 1 to z 'Zeilenindex
		For ii = 1 to s 'Spaltenindex
		if range(i,ii) <> ""  and range(i,ii) <> 0 and range(i,ii) <> " "  then
			outlist(c)= range(i,ii)
			c = c+1
		end if		
		next ii
	next i
	if c > 0 then
		redim preserve outlist(c-1)
		rverketten = join(outlist(),",")
	else
		rverketten = "# #"
	end if
	
else 
	rverketten = "# #"
end if
End function

Code: Alles auswählen

function sverketten(von , bis, tab ,sspalte )as string

	osheet = thisComponent.sheets( tab -1 )
	scol = sspalte -1
	srow = von-1
	erow = bis-1

	dim outlist(erow-srow)
	c = 0
	for i = srow to erow
		if osheet.getcellbyposition(i,scol).string = 0 then
			goto weiter:
		end if
		if isnumeric(osheet.getcellbyposition(scol,i).value)  then
			outlist(c) = osheet.getcellbyposition(scol,i).string
			c = c+1
		end if
		weiter:
	next i
		if c > 0 then
			redim preserve outlist(c-1)
			sverketten = join(outlist(),",")
		else
			sverketten ="###"
		end if
End function

Code: Alles auswählen

...
if osheet.getcellbyposition(i,srow).string = "" then '!!passe diese Zeile an
   goto weiter:
   end if
'      if isnumeric(osheet.getcellbyposition(i,srow).value)  then 'auskommentieren
         outlist(c) = osheet.getcellbyposition(i,srow).string
         c = c+1
'      end if  'auskommentieren
      weiter:
   next i
...

Code: Alles auswählen

function zverketten(von , bis, tab ,zzeile )as string

	osheet = thisComponent.sheets( tab -1 )
	srow = zzeile -1
	scol = von-1
	ecol = bis-1

	'adat() = osheet.getcellrangebyposition(scol,srow,ecol,srow).getdataarray
	dim outlist(ecol-scol)
	c = 0
	for i = scol to ecol
	if osheet.getcellbyposition(i,srow).value = 0 then
	goto weiter:
	end if
		if isnumeric(osheet.getcellbyposition(i,srow).value)  then
			outlist(c) = osheet.getcellbyposition(i,srow).string
			c = c+1
		end if
		weiter:
	next i
	if c > 0 then
		redim preserve outlist(c-1)
		zverketten = join(outlist(),",")
	else 
		zverketten ="###"
	end if


End function

Code: Alles auswählen

=ZVERKETTEN(COLUMN(F5);COLUMN(BX5);1;ROW(B5))

Code: Alles auswählen

if val(adat(0)(i)) = 0 then

Code: Alles auswählen

function zverketten(von , bis, tab ,zzeile )as string

	osheet = thisComponent.sheets( tab -1 )
	srow = zzeile -1
	scol = von-1
	ecol = bis-1

	adat() = osheet.getcellrangebyposition(scol,srow,ecol,srow).getdataarray
	dim outlist(ubound(adat(0)))
	c = 0
	for i = scol to ecol
	if val(adat(0)(i)) = 0 then
	goto weiter:
	end if
		if isnumeric(adat(0)(i))  then
			outlist(c)= adat(0)(i)
			c = c+1
		end if
		weiter:
	next i
	if c > 0 then
		redim preserve outlist(c-1)
		zverketten = join(outlist(),",")
	else 
		zverketten ="###"
	end if
End function

Code: Alles auswählen

=IF(SUMPRODUCT($Synthèse.$C$3:$C$150=$B5;$Synthèse.$I$3:$I$150=G$2);G2;0)