0

Basically I want to load a table into an Array in Google Sheets in VBA this would be easy. Then I want to search the array and find text and log the rows which that data is in.

In VBA this is what I would do.

Dim stArray(11, 1650) As String
Dim iRowLast As Int= 1650; Dim iColLast As Int = 10
Dim wsRaw as Workbook; Dim wsMap as Workbook
Set wsMap= Sheets("Map"); Set wsRaw= Sheets("Raw")

' Get the Array data from table '
For xRow = 0 To iRowLast
    For yRow = 0 to iColLast
        stArray(xRow, yRow) = wsRaw.Cells(xRow+1, iCol).Value
    Next yRow
Next xRow

'Send data to Worksheet'
For xRow = 0 To iRowLast
    For yRow = 0 to iColLast
        wsMap.Cells(xRow, yRow).Value = stArray(xRow+1, iCol+1)
    Next yRow
Next xRow

I tried something similar in Sheets and quickly found out that Multidimentional arrays don't exist.

I did find but don't know how to add to this array.

function Create2DArray(rows) 
{
    var arr = [];
    for (var i=0;i<rows;i++) 
    {
        arr[i] = [];
    }
return arr;
}
Crazyd
  • 406
  • 2
  • 7
  • 15

1 Answers1

1

I want to load a table into an Array in Google Sheets in VBA this would be easy.

It's much easier in Sheets. You don't need to load anything. It's automatically loaded and you automatically get a 2D array from getValues():

const ss = SpreadsheetApp.getActive();
const sheetRaw = ss.getSheetByName("Raw");
const sheetMap = ss.getSheetByName("Map");
const arr2D = sheetRaw.getRange(1,1,1650,11).getValues();
sheetMap.getRange(1,1,1650,11).setValues(arr2D);

Also see What does the range method getValues() return and setValues() accept?

TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Sheets is too slow to actually do this. I finally got it to work, but took forever just kept going on and on; logged it and it was working just taking forever – Crazyd Mar 03 '23 at 07:42
  • 1
    @JohnJohnson You're doing something wrong. If you bring archaic vba practices to modern JavaScript, it's going to be really really slow. The code I showed should take less than 5s. If you're unable to figure out a optimization process, You may ask a new question with your code after reviewing [best practices](https://developers.google.com/apps-script/guide_common_tasks#OptimizeScripts) and [this question](https://stackoverflow.com/questions/35289183/long-processing-time-likely-due-to-getvalue-and-cell-inserts) – TheMaster Mar 03 '23 at 07:44
  • 1
    I know I'm doing it all wrong... I don't know Java and Trying to do something above my head. https://docs.google.com/spreadsheets/d/1mS99uemTQS75CVSv9Zhbpudrqt-Vfy5ZtxjTjBDA_fo/edit?usp=sharing I want to search 4 columns for Text and paste results to worksheet. – Crazyd Mar 03 '23 at 07:52
  • 1
    @JohnJohnson I can't see code. Ask a new question with [mcve]. Sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer! See [How much research effort is expected of stackoverflow users?](https://meta.stackoverflow.com/questions/261592) See [ask]. It's ok, if you're trying to do something above your head, as long as you've tried something and can show that you've tried. – TheMaster Mar 03 '23 at 07:57
  • Just created that sheet https://docs.google.com/spreadsheets/d/1mS99uemTQS75CVSv9Zhbpudrqt-Vfy5ZtxjTjBDA_fo/edit?usp=sharing – Crazyd Mar 03 '23 at 08:02
  • 2
    I'll add a better question and work on it a bit more with the clue you gave me about getValues – Crazyd Mar 03 '23 at 08:05