[SOLVED] To Get Value as an Integer In a Cell by using LibreOffice Macro

English
, ,
#1

Hello!

I try to write a macro code. I have a problem. I want to get a value of a cell and I want to multiplication it with other cell and paste it.

I tried to multiplication it with many ways and I couldn’t it and I searched and couldn’t find it. Becauase of this I write here.

My code is here:

Sub CopyPaste

xA = CreateUnoStruct("com.sun.star.table.CellAddress") 
yA = CreateUnoStruct("com.sun.star.table.CellAddress")
tA = CreateUnoStruct("com.sun.star.table.CellAddress") 
zA = CreateUnoStruct("com.sun.star.table.CellAddress")

With xA 
  .Sheet       = 0
  .Column      = 1
  .Row         = iCount6
End With
With yA 
  .Sheet       = 0
  .Column      = 1
  .Row         = iCount6
End With


With tA 'For quantity
  .Sheet       = 0
  .Column      = 2
  .Row         = iCount6
End With
With zA 
  .Sheet       = 0
  .Column      = 2
  .Row         = iCount6
End With



For iCount6 = 1 to 6 'loops through rows 1 to 6
    xA.Row = iCount6
    yA.Row = iCount6
    tA.Row = iCount6
    zA.Row = iCount6
    SourceCell = ThisComponent.Sheets(xA.Sheet).GetCellByPosition(xA.Column, xA.Row)
    SourceCell = ThisComponent.Sheets(tA.Sheet).GetCellByPosition(tA.Column, tA.Row)
    PasteCell = (ThisComponent.Sheets(yA.Sheet).GetCellByPosition(yA.Column+2, yA.Row))*(ThisComponent.Sheets(zA.Sheet).GetCellByPosition(zA.Column+1, zA.Row))
    PasteCell.DataArray = SourceCell.DataArray
    
Next iCount6

End Sub

In the other way I tried to get value of a cell. I added its code here.

Sub CopyPaste1

xA = CreateUnoStruct("com.sun.star.table.CellAddress") 
yA = CreateUnoStruct("com.sun.star.table.CellAddress")

dim document as object
	document = ThisComponent
	sheet4 = document.Sheets(0)
	

For iCount6 = 1 to 6
	With xA 
	  .Sheet       = 0
	  .Column      = 1
	  .Row         = iCount6
	End With
	With yA 
	  .Sheet       = 0
	  .Column      = 1
	  .Row         = iCount6
	End With
	MsgBox(sheet4.getCellByPosition(2, iCount6).Integer)

    xA.Row = iCount6
    yA.Row = iCount6
    SourceCell = ThisComponent.Sheets(xA.Sheet).GetCellByPosition(xA.Column, xA.Row)
    PasteCell = ThisComponent.Sheets(yA.Sheet).GetCellByPosition(yA.Column+2, yA.Row)*(sheet4.getCellByPosition(2, iCount6).Integer)
    PasteCell.DataArray = SourceCell.DataArray
    
Next iCount6

End Sub

But here there is a problem about integer. If I write string, It works but not I want. I want to paste cell with quantity. So I can’t muliplication with String data.

I added example about them.

Copy-Paste Multiplication with a cell Example.ods

Edit for new example: 1.ods

Edit for @JohnSUN :

Sub copyMult
Dim oSheet As Variant, oRange As Variant
Dim oData As Variant    ' Data of range, all cells of all rows in range '
Dim i As Long   
    oSheet = ThisComponent.getSheets().getByIndex(0)
Rem Get entire range - this is a bit simpler than describing each cell
    oRange = oSheet.getCellRangeByName("B2:D7") 
Rem Get values of all cells in ramge to array 
    oData = oRange.getDataArray()
Rem Repeat for each row in array
'    For i = LBound(oData) To UBound(oData)
  '  For i = 2 To 7
Rem Take the integer part of values ​​of the first and second cells and multiply it
Rem Function CInt converts any string or numeric expression to an integer
		'Const sText3 = "Tahminleri Kopyalama Tamamlandı"
  	 '	MsgBox(CInt(oData(2)(2)))
        oData(3)(2) = CInt(oData(1)(2)) * CInt(oData(2)(2))
   ' Next i
Rem Return the computed array to the range
    oRange.setDataArray(oData)
End Sub

Thanks for your help.

Best Regard

#2

I hope this tutorial helps you understand the basics:

Sub copyMult
Dim oSheet As Variant, oRange As Variant
Dim oData As Variant	' Data of range, all cells of all rows in range '
Dim i As Long	
	oSheet = ThisComponent.getSheets().getByIndex(0)
Rem Get entire range - this is a bit simpler than describing each cell
	oRange = oSheet.getCellRangeByName("B2:D8")	
Rem Get values of all cells in ramge to array 
	oData = oRange.getDataArray()
Rem Repeat for each row in array
	For i = LBound(oData) To UBound(oData)
Rem Take the integer part of values ​​of the first and second cells and multiply it
Rem Function CInt converts any string or numeric expression to an integer
		oData(i)(2) = CInt(oData(i)(0)) * CInt(oData(i)(1))
	Next i
Rem Return the computed array to the range
	oRange.setDataArray(oData)
End Sub

Added: You don’t need to insert MsgBox or Print to know the value of a variable. Use the opportunity of Basic IDE

DebugCode1.png
DebugCode1.png751×600 49.4 KB

Chart macro vba to libre office
#3

@JohnSUN When I wrote MsgBox(CInt(oData(2)(2))) to get value of this cell, I get “0” even if It is “4”.

#4

Where do you insert this line? Perhaps, by this time, the value from column D, the third column of the third row (this is exactly what oData (2) (2) means) has not been calculated yet?

#5

@JohnSUN I edited my question and I added near “Edit for new example:” in my question

#6

@libremacrouser I edited my answer. By the way, changing the question did not help. Your second file is no different from the first - there are no macros. Apparently, your macro is not saved in the document, but in the “My Macros” library

#7

@JohnSUN I editted again it with my code.

#8

Your string “oData (3) (2) = CInt (oData (1) (2)) * CInt (oData (2) (2))” means “write in the cell D4 the product D2 and D3” - the first index is the row, the second is the column. Both start from zero.

We will not be able to solve your problem within this resource, the “Question-Answer” format is too small to solve your problem, you need to explain a lot. Please, go to any forum with this question or send me an email to Johnsun at i.ua

#9

@JohnSUN Thanks a lot. I solved my problem with your solution.

#10

@libremacrouser Please mark the answer as correct by clicking on its check mark. Thanks.

#11

Hi @JohnSUN in my cell there is value like 1,7 or 1.7. So When I cross it with another value, it doesn’t cross what I want because I use oData(i)(2) = CInt(oData(i)(0)) * CInt(oData(i)(1)). What can I do instead of them? Do you have any suggestion?

#12

Once more - your question was “To Get Value as an Integer…” As far as I understand the word Integer it is 1 or 2, and not somewhere in the middle, about 1.7. Who of us is wrong?

#13

@JohnSUN There isn’t wrong. I run it correctly when you said me, after that I need to cross value like 1.7 so I need to ask you whether can I cross it or not

#14

Of course you can! Just change CInt(…) to a more complex structure - Val(Replace(…, “,”, “.”)). So, this line will be like as oData(i)(2) = Val(Replace(oData(i)(0), ",", ".")) * Val(Replace(oData(i)(1), ",", "."))

#15

@JohnSUN I solved my this problem thanks to you. Thanks a lot

#16

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.