excel 互惠vlookup函数-帮助从VBA转换到谷歌工作表应用程序脚本

我对VBA和Google Sheets应用程序脚本都相当不熟悉。
话虽如此，我昨天能够谷歌足够弄清楚我想在VBA中实现什么。为了让分享我的电子表格更容易，我想试着把它转换成谷歌表单。我已经猜出了大部分，除了这一部分。
这是主表（只有一个人需要看到）。在Excel中，人们可以将数据（以正确的格式）粘贴到B列的橙子输入字段中，然后将文本拆分到其下方的黄色字段中。

C列中的字段将使用B列中的键根据多个参考工作表的vlookup自动更新。

从这里，我可以编辑B或C列，另一列则更新以保持它们同步。我不得不在参考表中将A列复制到C列，因为vlookup只能从左到右工作，而我无法让xlookup工作。
下面是我工作的草率VBA代码。我肯定这是一个烂摊子，但它的工作完美的我所需要的

Private Sub Worksheet_change(ByVal Target As Range)
If Target.Address = "$B$2" Then
    If Len("B2") > 1 Then
        Dim regex As Object
        Dim X As Variant
        Dim Y As Variant
        Set regex = CreateObject("VBScript.RegExp")
        regex.Pattern = "font[0-9]{1,2}="
        Y = Replace(Range("B2").Value, "(", "")
        Y = Replace(Y, ")", "")
        Y = regex.Replace(Y, "")
        X = Split(Y, ",")
        Range("B3").Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
        Range("C6").Value = Application.WorksheetFunction.VLookup(Range("B6"), Sheets("A (Hum)").Range("A:B"), 2, 0)
        Range("C7").Value = Application.WorksheetFunction.VLookup(Range("B7"), Sheets("B (Blaster)").Range("A:B"), 2, 0)
        Range("C8").Value = Application.WorksheetFunction.VLookup(Range("B8"), Sheets("C (Force)").Range("A:B"), 2, 0)
        Range("C9").Value = Application.WorksheetFunction.VLookup(Range("B9"), Sheets("D (Lockup)").Range("A:B"), 2, 0)
        Range("C10").Value = Application.WorksheetFunction.VLookup(Range("B10"), Sheets("E (FoC)").Range("A:B"), 2, 0)
        Range("C11").Value = Application.WorksheetFunction.VLookup(Range("B11"), Sheets("F (Ignition)").Range("A:B"), 2, 0)
    End If
End If

'Stop the reciprocal vlookups causing an error
Application.EnableEvents = False

' IF Column B is selected, changed column C
If Target.Address = "$B$6" Then
    Range("C6").Value = Application.WorksheetFunction.VLookup(Range("B6"), Sheets("A (Hum)").Range("A:B"), 2, 0)
End If
If Target.Address = "$B$7" Then
    Range("C7").Value = Application.WorksheetFunction.VLookup(Range("B7"), Sheets("B (Blaster)").Range("A:B"), 2, 0)
End If
If Target.Address = "$B$8" Then
    Range("C8").Value = Application.WorksheetFunction.VLookup(Range("B8"), Sheets("C (Force)").Range("A:B"), 2, 0)
End If
If Target.Address = "$B$9" Then
    Range("C9").Value = Application.WorksheetFunction.VLookup(Range("B9"), Sheets("D (Lockup)").Range("A:B"), 2, 0)
End If
If Target.Address = "$B$10" Then
    Range("C10").Value = Application.WorksheetFunction.VLookup(Range("B10"), Sheets("E (FoC)").Range("A:B"), 2, 0)
End If
If Target.Address = "$B$11" Then
    Range("C11").Value = Application.WorksheetFunction.VLookup(Range("B11"), Sheets("F (Ignition)").Range("A:B"), 2, 0)
End If

' And Vice Versa
If Target.Address = "$C$6" Then
    Range("B6").Value = Application.WorksheetFunction.VLookup(Range("C6"), Sheets("A (Hum)").Range("B:C"), 2, 0)
End If
If Target.Address = "$C$7" Then
    Range("B7").Value = Application.WorksheetFunction.VLookup(Range("C7"), Sheets("B (Blaster)").Range("B:C"), 2, 0)
End If
If Target.Address = "$C$8" Then
    Range("B8").Value = Application.WorksheetFunction.VLookup(Range("C8"), Sheets("C (Force)").Range("B:C"), 2, 0)
End If
If Target.Address = "$C$9" Then
    Range("B9").Value = Application.WorksheetFunction.VLookup(Range("C9"), Sheets("D (Lockup)").Range("B:C"), 2, 0)
End If
If Target.Address = "$C$10" Then
    Range("B10").Value = Application.WorksheetFunction.VLookup(Range("C10"), Sheets("E (FoC)").Range("B:C"), 2, 0)
End If
If Target.Address = "$C$11" Then
    Range("B11").Value = Application.WorksheetFunction.VLookup(Range("C11"), Sheets("F (Ignition)").Range("B:C"), 2, 0)
End If

'Re-enable events after updates
Application.EnableEvents = True

End Sub

只是为了有一个答案，为其他任何人谷歌，我确实有它的工作。当然可以改进，但它以相同方式工作的第一次迭代如下。不像excel那样即时，但功能相同

function onEdit(e) {
  if (e.range.getA1Notation() === 'B2') {
    var input = SpreadsheetApp.getActiveSheet().getRange('B2').getValue();
    if (input.length > 0) {
      var regex = new RegExp('font[0-9]{1,2}=');
      var input = input.replace(regex,"");
      var input = input.replace("(","");
      var input = input.replace(")","");
      var array = input.split(',');
      SpreadsheetApp.getActiveSheet().getRange("B3").setValue(array[0]);
      SpreadsheetApp.getActiveSheet().getRange("B4").setValue(array[1]);
      SpreadsheetApp.getActiveSheet().getRange("B5").setValue(array[2]);
      SpreadsheetApp.getActiveSheet().getRange("B6").setValue(array[3]);
      SpreadsheetApp.getActiveSheet().getRange("B7").setValue(array[4]);
      SpreadsheetApp.getActiveSheet().getRange("B8").setValue(array[5]);
      SpreadsheetApp.getActiveSheet().getRange("B9").setValue(array[6]);
      SpreadsheetApp.getActiveSheet().getRange("B10").setValue(array[7]);
      SpreadsheetApp.getActiveSheet().getRange("B11").setValue(array[8]);
      SpreadsheetApp.getActiveSheet().getRange("B12").setValue(array[9]);
      SpreadsheetApp.getActiveSheet().getRange("B13").setValue(array[10]);

      SpreadsheetApp.getActiveSheet().getRange("C6").setValue(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("A (Hum)").getRange('A:B').getValues()[SpreadsheetApp.getActiveSpreadsheet().getSheetByName("A (Hum)").getRange('A:B').getValues().map(x => x[0]).indexOf(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("B6").getValue())][1]);

      SpreadsheetApp.getActiveSheet().getRange("C7").setValue(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("B (Blaster)").getRange('A:B').getValues()[SpreadsheetApp.getActiveSpreadsheet().getSheetByName("B (Blaster)").getRange('A:B').getValues().map(x => x[0]).indexOf(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("B7").getValue())][1]);

      SpreadsheetApp.getActiveSheet().getRange("C8").setValue(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("C (Force)").getRange('A:B').getValues()[SpreadsheetApp.getActiveSpreadsheet().getSheetByName("C (Force)").getRange('A:B').getValues().map(x => x[0]).indexOf(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("B8").getValue())][1]);

      SpreadsheetApp.getActiveSheet().getRange("C9").setValue(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("D (Lockup)").getRange('A:B').getValues()[SpreadsheetApp.getActiveSpreadsheet().getSheetByName("D (Lockup)").getRange('A:B').getValues().map(x => x[0]).indexOf(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("B9").getValue())][1]);

      SpreadsheetApp.getActiveSheet().getRange("C10").setValue(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("E (FoC)").getRange('A:B').getValues()[SpreadsheetApp.getActiveSpreadsheet().getSheetByName("E (FoC)").getRange('A:B').getValues().map(x => x[0]).indexOf(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("B10").getValue())][1]);

      SpreadsheetApp.getActiveSheet().getRange("C11").setValue(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("F (Ignition)").getRange('A:B').getValues()[SpreadsheetApp.getActiveSpreadsheet().getSheetByName("F (Ignition)").getRange('A:B').getValues().map(x => x[0]).indexOf(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("B11").getValue())][1]);

      
    }  
  }

  /* A (Hum) */
  if (e.range.getA1Notation() === 'B6') {
    SpreadsheetApp.getActiveSheet().getRange("C6").setValue(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("A (Hum)").getRange('A:B').getValues()[SpreadsheetApp.getActiveSpreadsheet().getSheetByName("A (Hum)").getRange('A:B').getValues().map(x => x[0]).indexOf(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("B6").getValue())][1]);
  }
  if (e.range.getA1Notation() === 'C6') {
    SpreadsheetApp.getActiveSheet().getRange("B6").setValue(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("A (Hum)").getRange('B:C').getValues()[SpreadsheetApp.getActiveSpreadsheet().getSheetByName("A (Hum)").getRange('B:C').getValues().map(x => x[0]).indexOf(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("C6").getValue())][1]);
  }
  /* B (Blaster) */
  if (e.range.getA1Notation() === 'B7') {
    SpreadsheetApp.getActiveSheet().getRange("C7").setValue(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("B (Blaster)").getRange('A:B').getValues()[SpreadsheetApp.getActiveSpreadsheet().getSheetByName("B (Blaster)").getRange('A:B').getValues().map(x => x[0]).indexOf(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("B7").getValue())][1]);
  }
  if (e.range.getA1Notation() === 'C7') {
    SpreadsheetApp.getActiveSheet().getRange("B7").setValue(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("B (Blaster)").getRange('B:C').getValues()[SpreadsheetApp.getActiveSpreadsheet().getSheetByName("B (Blaster)").getRange('B:C').getValues().map(x => x[0]).indexOf(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("C7").getValue())][1]);
  }

  /* C (Force) */
  if (e.range.getA1Notation() === 'B8') {
    SpreadsheetApp.getActiveSheet().getRange("C8").setValue(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("C (Force)").getRange('A:B').getValues()[SpreadsheetApp.getActiveSpreadsheet().getSheetByName("C (Force)").getRange('A:B').getValues().map(x => x[0]).indexOf(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("B8").getValue())][1]);
  }
  if (e.range.getA1Notation() === 'C8') {
    SpreadsheetApp.getActiveSheet().getRange("B8").setValue(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("C (Force)").getRange('B:C').getValues()[SpreadsheetApp.getActiveSpreadsheet().getSheetByName("C (Force)").getRange('B:C').getValues().map(x => x[0]).indexOf(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("C8").getValue())][1]);
  }

  /* D (Lockup) */
  if (e.range.getA1Notation() === 'B9') {
    SpreadsheetApp.getActiveSheet().getRange("C9").setValue(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("D (Lockup)").getRange('A:B').getValues()[SpreadsheetApp.getActiveSpreadsheet().getSheetByName("D (Lockup)").getRange('A:B').getValues().map(x => x[0]).indexOf(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("B9").getValue())][1]);
  }
  if (e.range.getA1Notation() === 'C9') {
    SpreadsheetApp.getActiveSheet().getRange("B9").setValue(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("D (Lockup)").getRange('B:C').getValues()[SpreadsheetApp.getActiveSpreadsheet().getSheetByName("D (Lockup)").getRange('B:C').getValues().map(x => x[0]).indexOf(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("C9").getValue())][1]);
  }

  /* E (FoC) */
  if (e.range.getA1Notation() === 'B10') {
    SpreadsheetApp.getActiveSheet().getRange("C10").setValue(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("E (FoC)").getRange('A:B').getValues()[SpreadsheetApp.getActiveSpreadsheet().getSheetByName("E (FoC)").getRange('A:B').getValues().map(x => x[0]).indexOf(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("B10").getValue())][1]);
  }
  if (e.range.getA1Notation() === 'C10') {
    SpreadsheetApp.getActiveSheet().getRange("B10").setValue(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("E (FoC)").getRange('B:C').getValues()[SpreadsheetApp.getActiveSpreadsheet().getSheetByName("E (FoC)").getRange('B:C').getValues().map(x => x[0]).indexOf(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("C10").getValue())][1]);
  }

  /* F (Ignition) */
  if (e.range.getA1Notation() === 'B11') {
    SpreadsheetApp.getActiveSheet().getRange("C11").setValue(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("F (Ignition)").getRange('A:B').getValues()[SpreadsheetApp.getActiveSpreadsheet().getSheetByName("F (Ignition)").getRange('A:B').getValues().map(x => x[0]).indexOf(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("B11").getValue())][1]);
  }
  if (e.range.getA1Notation() === 'C11') {
    SpreadsheetApp.getActiveSheet().getRange("B11").setValue(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("F (Ignition)").getRange('B:C').getValues()[SpreadsheetApp.getActiveSpreadsheet().getSheetByName("F (Ignition)").getRange('B:C').getValues().map(x => x[0]).indexOf(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("C11").getValue())][1]);
  }

}

excel 互惠vlookup函数-帮助从VBA转换到谷歌工作表应用程序脚本

1条答案

相关问题

热门标签

最新问答