Ask Your Question
0

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

asked 2018-12-04 07:03:59 +0200

libremacrouser gravatar image

updated 2018-12-04 12:45:17 +0200

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.

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 ...
(more)
edit retag flag offensive 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

1 Answer

Sort by » oldest newest most voted
1

answered 2018-12-04 07:52:58 +0200

JohnSUN gravatar image

updated 2018-12-04 09:38:22 +0200

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

edit flag offensive delete link more

Comments

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

libremacrouser gravatar imagelibremacrouser ( 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?

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

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

libremacrouser gravatar imagelibremacrouser ( 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

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

@JohnSUN I editted again it with my code.

libremacrouser gravatar imagelibremacrouser ( 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

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

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

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

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

erAck gravatar imageerAck ( 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?

libremacrouser gravatar imagelibremacrouser ( 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?

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

Question Tools

1 follower

Stats

Asked: 2018-12-04 07:03:59 +0200

Seen: 107 times

Last updated: Dec 04 '18