Faster output to Sheet from a collection of Dictionnaries

My code is currently offloading parsed data into multiple spreadsheets, but it’s pretty slow about 110k rows and 35 columns in 3.5 min I was wondering if there were a more efficient way of looping through my collection of dictionaries.

Sub getData(Parsed As Object, company As String, a, wb)         'Parsed contains a collection of dictionaries parsed from a JSON input'          Dim oSheet As Variant         Dim i As Long         Dim j As Long         For Each oSheet In ActiveWorkbook.Sheets             'Identify the sheet containing the name of the company'             Dim matchSheet As String             If InStr(UCase(oSheet.Name), UCase(company)) Then                 matchSheet = oSheet.Name             Else                 GoTo nextIteration             End If                 'Create a 2d array of i number of dictionaries and j number of element in each dictionary'                 Dim Values As Variant                 ReDim Values(Parsed.Count, ArrayLen(a))                  Dim Value As Dictionary                 Dim key As Variant                  i = 1                 j = 0                 wb.Sheets(matchSheet).Select 'macro not working without selecting the sheet'                 wb.Sheets(matchSheet).Cells.Clear 'Clearing the data in the company Sheet'                  'For loops populating the Values array'                 For Each Value In Parsed                     For Each key In a                         Values(i, j) = Value(key)                         j = j + 1                     Next key                     j = 0                   i = i + 1                 Next Value                  'populating the needed sheet range with the parsed data'                 wb.Sheets(matchSheet).Range(Cells(1, 1), Cells(Parsed.Count, ArrayLen(a))) = Values                 'populationg the headers for that data'                 wb.Sheets(matchSheet).Range(Cells(1, 1), Cells(1, ArrayLen(a))).Value = a                 wb.Sheets(matchSheet).Range("D:D").NumberFormat = "General" nextIteration:         Next oSheet  End Sub ``` 

Why is “Corruption” listed as its own saving throw on the Adventures in Middle Earth character sheet?

On the character sheet, under the Saving Throws section, there is “Corruption” listed. I can fill in the “proficiency” bubble or write a modifier in.

My question is: What am I supposed to use that part of the sheet to keep track of? Resisting Corruption is done by making a Wisdom saving throw, which is already on the sheet, so I don’t understand why there’s a separate saving throw proficiency for “Corruption” saving throws, whatever those are.

Auto Create New Google Sheet With Column Headers Based on List of Names

I’m trying to create a VERY basic CRM tool. I want to create a running list of customer names. Once I enter a name in Column A I’d like a new Sheet automatically created with pre-set columns I can enter values and information into. The columns I’d like are:

1. Name 2. Address 3. Phone Number 4. Email Address 5. Quote 6. Sold 

Also, I’d like to be able to click on the name and be taken directly to the corresponding sheet that has been created.

App Script to move a row of data from one google sheet to a different one (different sheet id’s) based on a cell entry

I’m currently trying to adjust a script I found that copies the contents of a row from one tab of a google sheet, pastes it into the next descending empty row in a different tab and then deletes the original row.

function onEdit() {    var sheetNameToWatch = "Original_Sheet";    var columnNumberToWatch = 3;   var valueToWatch = "ValueToLookFor";   var sheetNameToMoveTheRowTo = "Target_Sheet";    var ss = SpreadsheetApp.getActiveSpreadsheet();   var sheet = SpreadsheetApp.getActiveSheet();   var range = sheet.getActiveCell();     if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {      var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);     var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);     sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);     sheet.deleteRow(range.getRow());   } } 

What I want to do from here is be able to run the same script but with the target sheet to be a sheet located on a completely different google sheet instead of from tab to tab within the same google sheet.

I have next to zero coding knowledge so unsure how I would edit it from here. Thank you in advance!

I’m reading all lines of another sheet and I paste the value that I want to the active sheet VBA

I have a program that read thousands of lines and return the value that I need. The problem is that request take a lot of time probably like 1min just to search and paste the value, even when I’m saving the code it takes a lot of time…

I think it is around this line that the code is slow :

If Sheets("MT950").Cells(line, 1) Like "-}{5:*" Then 

Here is my code :

Function mysolde62(mycurrency As String, swift As String) As Double  Dim SearchString As String Dim LastLine As Long, line As Long, k As Long Dim mybegin As Long, myend As Long, test As Long, count As Long Dim sign As String Dim myvalue As Double  LastLine = Sheets("MT950").Range("A1").End(xlDown).Row  count = 0 myend = 1  For line = 1 To LastLine  If Sheets("MT950").Cells(line, 1) Like "-}{5:*" Then  SearchString = Sheets("MT950").Range("A" & line).Value  mypos = InStr(1, SearchString, swift, 1) If mypos <> 0 Then     count = count + 1 End If  End If Next line  For k = 1 To count  For line = myend To LastLine If Sheets("MT950").Cells(line, 1) Like "-}{5:*" Then     SearchString = Sheets("MT950").Range("A" & line).Value      mypos = InStr(1, SearchString, swift, 1)     If mypos <> 0 Then          mybegin = line         For linebis = mybegin To LastLine             If Sheets("MT950").Cells(linebis, 1) Like ":62F:*" Then                 SearchString = Sheets("MT950").Range("A" & linebis).Value                  mypos = InStr(1, SearchString, mycurrency, 1)                 If mypos <> 0 Then                     myend = linebis                     test = 1                 End If                 Exit For             End If         Next linebis          If test = 1 Then Exit For     End If End If Next line If test = 1 Then Exit For Next k   sign = Mid(Sheets("MT950").Cells(myend, 1).Value, 5, 1) myvalue = Mid(Sheets("MT950").Cells(myend, 1).Value, 15) If sign = "D" Then mysolde62 = -myvalue Else mysolde62 = myvalue End If   End Function 

Dynamic data validation in google sheet

I want to restrict a value in a cell based on another cell in same row.

The value in A must not exceed what is in B. If the value in B is zero, any figure is allowed in A


4 9 – allowed

5 0 – allowed

6 5 – the value in A should not be allowed

How to set up data validation for this in column A.

I want all rows in Column A to have this validation rule.