在 VBA 中将变量写入单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41029913/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Write Variable To Cell In VBA
提问by StarsFlyFree FromCozyNights
I am needing to follow the protocol below:
I am scanning Sheet1 and for each unique empName on that worksheet selecting the individual empName worksheet.
on the individual empName worksheet capturing the value in the last cell in column O
Storing the value in variable tper (it's a percentage)
Selecting sheet1
Writing a header to column N1
Selecting the 1st empty cell in column N (excluding the header)
write the value of tper to the selected cell in column N
Repeat until all empNames have been processed from Sheet1
我需要遵循以下协议：
我正在扫描 Sheet1 并为该工作表上的每个唯一 empName 选择单个 empName 工作表。
在单个 empName 工作表上捕获 O 列最后一个单元格中
的值 将值存储在变量 tper 中（它是一个百分比）
选择 sheet1
将标题写入 N1 列
选择 N 列中的第一个空单元格（不包括标题）
写入值tper 到 N 列中的选定单元格
重复直到从 Sheet1 处理完所有 empNames
My syntax seems to execute as it should up until this line lr1 = Cells(Rows.Count, 13).End(xlUp).Rowwhere it throws an error of
我的语法似乎按照它应该执行的方式执行，直到lr1 = Cells(Rows.Count, 13).End(xlUp).Row它抛出错误的这一行
error invalid qualifier
错误无效限定符
What do I need to re-write in order for this to follow the protocol outlined above?
为了遵循上述协议，我需要重写什么？
Function Test()
Dim lr As Long, i As Long, lr1 As Long, i1 As Long
Dim WS As Worksheet, empName As String, tper As Variant
Set WS = ActiveSheet
lr = Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To lr
empName = WS.Cells(i, 2).Value
Sheets(empName).Select
tper = "=LOOKUP(2,1/(O:O<>""),O:O)"
Sheets("Sheet1").Select
Range("N1").FormulaR1C1 = "Percent"
lr1 = Cells(Rows.Count, 13).End(xlUp).Row
For i1 = 2 To lr1
lr1.Cells.FormulaR1C1 = tper
Next i1
Next i
End Function
回答by nightcrawler23
I have attempted to modify your code. see if this works. Since you want to loop through all employees in Sheet1, which is being taken care of by the first for loop, I got rid of the second loop.
我试图修改你的代码。看看这是否有效。由于您想遍历由第一个 for 循环处理的 Sheet1 中的所有员工，因此我摆脱了第二个循环。
Sub Test()
Dim empName As String, tper As Variant
Dim WS As Worksheet, empSheet As Worksheet
Set WS = Sheets("Sheet1")
Dim lr As Long
lr = WS.Cells(Rows.Count, 2).End(xlUp).Row
Dim i As Long
For i = 2 To lr
'scanning Sheet1 and for each unique empName
empName = WS.Cells(i, 2).Value
'selecting the individual empName worksheet
'just set to variable. no need to select
Set empSheet = Sheets(empName)
'on the individual empName worksheet capturing the value in the last cell in column O
'Storing the value in variable tper (it's a percentage)
tper = empSheet.Range("O" & Rows.Count).End(xlUp).Value
'Selecting Sheet1
'Writing a header to column N1
WS.Range("N1").FormulaR1C1 = "Percent"
'Selecting the 1st empty cell in column N (excluding the header)
WS.Range("N" & Rows.Count).End(xlUp).Offset(1, 0) = tper
'Repeat until all empNames have been processed from Sheet1
' next i will reapeat for the next employee in sheet 1
Next i
End Sub
You also mentioned
你还提到
for each unique empName
对于每个唯一的 empName
The code does not check that.
该代码不检查。
