我写了一个脚本在谷歌应用程序脚本中搜索表格中的值,并按照用户输入的更新行values.It当我下载文件为excel文件时,谷歌sheets.But的工作很好,我甚至希望脚本被下载并转换为VBA宏,但这并没有发生,因为我没有任何编写VBA脚本的经验,有没有任何tol,可以用来转换的.gs到VBA。如果有帮助,下面是函数:
function test() {
var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');
var spreadsheet = SpreadsheetApp.getActive();
sheet2.getRange('B5:B7').activate();
var idForSearch = sheet2.getRange("B5").getValue();
var data = sheet1.getDataRange().getValues();
var rownumber;
for(var i = 0; i<data.length;i++){
if(data[i][0] == idForSearch){ //[0] because column A
Logger.log((i+1))
rownumber= i+1;
}
}
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet1'), true);
spreadsheet.getRange('A'+rownumber+':'+'C'+rownumber).activate();
spreadsheet.getRange('Sheet2!B5:B7').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true);
};
提前感谢您的帮助。
发布于 2020-01-26 21:13:08
请测试下一段代码。我没有你要处理的数据,我只是在理论上,没有测试...
Private Sub testConvert()
Dim actWork As Workbook, sheet1 As Worksheet, sheet2 As Worksheet
Dim data As Variant, i As Long, rownumber As Long, rng As Range
Set actWork = ActiveWorkbook
Set sheet1 = actWork.Sheets("Sheet1")
Set sheet2 = actWork.Sheets("Sheet2")
actWork.Activate
idForSearch = shee2.Range("B5").Value
data = sheet1.Range("B5:B7").Value 'not necessary to be activated
For i = 1 To UBound(data) 'in VBA such an array (created from a range) is 1 based...
If UCase(data(i,1)) = UCase(idForSearch) Then 'VBA is case sensitive
WriteLog i 'you must have a function ('WriteLog') able to do it...
rownumber = i
'if you have only one occurrence, it is good to exit the loop to save time (not for this specific case of 3 times):
Exit For
'If many occurrences may be, you just comment the line above...
End If
Next i
sheet1.Activate 'not necessary
Set rng = sheet1.Range("A" & rownumber & ":" & "C" & rownumber).Select
sheet2.Range("B5:B7").Value = WorksheetFunction.Transpose(rng.Value)
End Sub
最后一个代码行将sheet2
的列范围上的sheet1
的水平范围转置...
我可以帮助你在函数WriteLog
创建,如果你告诉我一些更多的细节:它必须是什么类型的日志(文本文件),在该路径上要创建,找到等。你想添加,附加的i
值,或你想重写现有的一个,如果任何值已经被记录?
https://stackoverflow.com/questions/59918478
复制相似问题