Cannot get an excel macro to work in google docs

[boccuz]

Hello,

I have a macro that works in an Excel worksheet, but when copying to a google doc, it does not. I am hoping that I can get help with converting the macro to work in google docs.

Here is the macro that works in Excel.

Sub ConvertRunNumber()
    For x = 1 To 8000
        RunNum = Cells(x, 1).Value
        Newrunnum = Left(RunNum, 8)
        Cells(x, 2).Value = Newrunnum
        Range("B2").Value = Newrunnum
        Cells(x, 2).NumberFormat = "0"
    Next x
End Sub

Here is the macro in google docs that fails.




Syntax error: SyntaxError: Unexpected identifier 'ConvertRunNumber' line: 2 file: Missing Numbers.gs

[Aussiebear]

It's important to understand that Excel VBA and Google Docs (specifically Google Sheets) use different scripting languages. Therefore, you cannot directly run Excel VBA code in Google Docs.

Google provides the "Macro Converter" add on. This can help with converting some VBA macro's to Google Apps Script. However it is not a perfect process, and often requires manual adjustment of the resulting script.

[Aussiebear]

Maybe try this

Function convertRunNumber()
    { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var lastRow = Math.min(8000, sheet.getLastRow()); // Limit to 8000 rows or the last row with data  
    For (var x = 1; x <= lastRow; x++) { var runNum = sheet.getRange(x, 1).getValue(); // Get value from column A
        If (runNum) { // Check if the cell has a value
            var newRunNum = String(runNum).substring(0, 8); // Extract the first 8 characters
            sheet.getRange(x, 2).setValue(newRunNum); // Write to column B
            If (x === 2){ // Added to replicate the Range("B2").value=Newrunnum part of the vba code. sheet.getRange(2,2).setValue(newRunNum);
                } sheet.getRange(x, 2).setNumberFormat("0"); // Set number format to integer
            }
        }
    }

[boccuz]

Thank you Aussiebear! It worked!!

[Aussiebear]

Well its nice to finally have a winner for the evening... batting averages are down for the last week.