CODEWITHSUNDEEP
excelvbatypescriptoffice-scripts
Teco
Teco

Reputation: 5

How to convert For loop in VBA to Typescript in a right way

Im trying to convert a Macro VBA to a Office Script(Typescript) to run in Excel Web

I have this piece of VBA Code:

Sub process()
        On Error GoTo Error_Handler
    ' concatenate Macro

    Dim var As String
    Dim row As Integer
    Dim column As Integer
    Dim concatened As String
    Dim hours() As String
    Dim cursor As Integer
    Dim person As Integer
    Dim sum As Double
    
    ' zeroing columns

    For column = Sheets("Parameters").Cells(2, 4) To Sheets("Parameters").Cells(2, 5)
        For row = Sheets("Parameters").Cells(2, 2) To Sheets("Parameters").Cells(2, 3)
            
            Sheets("Projects").Cells(row, column) = ""
            
        Next row
        
        Sheets("Projects").Cells(3, column) = ""
        
    Next column

The context is: I have the main Worksheet who's called Projects, then this loop scan the columns and rows determined by the worksheet called Parameters and erase the data in it

Cells 2,4; 2,5; 2,2; 2,3 just tell them where to start and where to stop by columns and rows

At first I did:

    //catch active worksheet
  let selectedWorksheet = workbook.getActiveWorksheet();
  

  // select range to scan
  let range = selectedWorksheet.getRange("EK3:HL3");

  // zeroing columns
  range.setValue("");

But I'd like to automate the "EK3:HL3" like the VBA code, to edit by the Parameters worksheet rather than changing manually in the code in case the range EK3:HL3 gets bigger....

If you guys could bright my mind, i'd be truly glad

Upvotes: 0

Views: 1027

Answers (1)

norie
norie

Reputation: 9857

You could try using getRangeByIndexes.

    const wsParameters = Workbook.getWorksheet("Parameters")
    const wsProjects = Workbook.getWorksheet("Projects")
    
    const startRow = wsParameters.getRange("B2").getValue()
    const startCol = wsParameters.getRange("D2").getValue()
    const noRow = wsParameters.getRange("C2").getValue() - startRow + 1
    const noCols = wsParameters.getRange("E2").getValue() - startCol + 1

    let rng = wsProjects.getRangeByIndexes(startRow, startCol, noRow, noCols)

    rng.setValue("")

Upvotes: 1

Related Questions