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

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

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

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.

C:\fakepath\Copy-Paste Multiplication with a cell Example.ods

Edit for new example: C:\fakepath\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 ...
edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by erAck close date 2018-12-04 15:18:26.366609

Sort by » oldest newest most voted

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

more

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

( 2018-12-04 08:59:46 +0200 )edit

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?

( 2018-12-04 09:05:09 +0200 )edit

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

( 2018-12-04 09:09:17 +0200 )edit

@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

( 2018-12-04 09:32:26 +0200 )edit

@JohnSUN I editted again it with my code.

( 2018-12-04 09:40:17 +0200 )edit

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

( 2018-12-04 09:52:39 +0200 )edit

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

( 2018-12-04 12:44:23 +0200 )edit

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

( 2018-12-04 13:59:53 +0200 )edit

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?

( 2018-12-22 07:01:32 +0200 )edit

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?

( 2018-12-22 08:43:35 +0200 )edit