Cannot calculate numbers via Macro

Docs API
1

Hello,

Im converting an simple vba script to onlyoffice api.

Now I have the problem that I can’t add up two values correctly, it’s always just expanded like a string.
The first value is the value of the column above the current row, the second is simple +1.

This is my current Code:

`(function ()
{
function formatDate(d) {
var month = ‘’ + (d.getMonth() + 1),
day = ‘’ + d.getDate(),
year = d.getFullYear();

    if (month.length < 2) 
        month = '0' + month;
    if (day.length < 2) 
        day = '0' + day;

    return [day, month, year].join('.');
}

var currentDate = formatDate(new Date());

// Getting the active sheet
var activeSheet = Api.ActiveSheet;
// Minimum row index
var indexRowMin = 0;
// Maximum row index
var indexRowMax = 1048576;
// Column 'A'
var indexCol = 0;
// Row index for empty cell search
var indexRow = indexRowMax;
for (; indexRow >= indexRowMin; --indexRow) {
    // Getting the cell
    var range = activeSheet.GetRangeByNumber(indexRow, indexCol);
    // Checking the value
    if (range.GetValue() && indexRow !== indexRowMax) {
        range = activeSheet.GetRangeByNumber(indexRow + 1, indexCol);
        
        range.Select();
        
        range.GetCells(1, 1).SetValue(range.GetCells(-1, 1).GetValue() + 1)
        range.GetCells(1, 2).SetValue(currentDate)
        break;
    }
}

})();`

The Result:
2023-08-17 20_36_27-Window

I just want to have a simple ascending number row, but it needs to be added by an macro (button).

Thank you for the help in advance.

Erik

2

hi @ee97 :handshake:

Can you send a document with a macro?

3

I would like to upload my file, but I cannot “Sorry, new users can not upload attachments.”.

The VBA code is the following:

Private Sub neuerEinsatz_Btn_Click()
    LastRow = Cells(65000, 1).End(xlUp).Offset(1, 0).Row
    Cells(LastRow, "A").Value = Cells(LastRow - 1, 1).Value + 1
    Cells(LastRow, "B").Value = CDate(Format(Now, "dd.mm.yyyy"))
    Cells(LastRow, "C").Value = CDate(Format(Now, "hh:mm:ss"))
End Sub
1 Like
4

@ee97

try now

5

Sorry for the late reply, the topic got lost in time.

This is the macro file, which I would like to migrate to onlyoffice:
Entwurf_Public.xlsx (40.6 KB)
(I had to change the file extension from xlsm to xlsx for uploading)

6

Hello,

Actually, GetValue() return a string. So + mean ‘concatenate’ these strings.
To compute, you need to evaluate the string you got with GetValue() with eval() function.

eval( range.GetCells(-1,1).GetValue() ) + 1 will work…

Here a similar code I just wrote :

(function()
{
    const sheet = Api.GetActiveSheet();
    sheet.GetRange("A1").Value = 1;

    var index = 0;
    do {
        sheet.GetRangeByNumber( index + 1, 0 ).Value = eval(
            sheet.GetRangeByNumber( index, 0 ).Value ) + 1;
        index += 1;
    } while ( index < 10 );
    
})();

Hope this helps…
Franck

1 Like