带有 if 函数的 VBA 代码（取自单元格的值）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15391351/
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
VBA code with if function (values taken from the cell)
提问by mgunia
I have VBA code which uses countif function and checks if the value "United States" is populated in the Test column (see the code below).
我有使用 countif 函数的 VBA 代码，并检查“测试”列中是否填充了值“美国” （请参阅下面的代码）。
I would like to make this more flexible and I want it to get the data from the specific column (e.g. A2) instead of static value "United States".
我想让它更灵活，我希望它从特定列（例如 A2）而不是静态值“美国”获取数据。
I know that this piece of code should be amended
我知道这段代码应该修改
Debug.Print "=IF(RC[" & CStr(FormulaCol - LookupCol - 1) & "]=""United States"",1,0)"
ActiveCell.Offset(0, FormulaCol - 1).FormulaR1C1 = "=IF(RC[" & CStr(LookupCol - FormulaCol) & "]=""United States"",1,0)"
I tried to use the simple function code =IF(B2=A2,1,0) and put A2 in the code. All my tries returned the syntax error.
我尝试使用简单的函数代码 =IF(B2=A2,1,0) 并将 A2 放入代码中。我所有的尝试都返回了语法错误。
My Macro
我的宏
Sub bbb()
Dim FormulaCol As Long
Dim LookupCol As Long
Dim TotalRows As Long
Dim TotalCols As Long
Dim i As Long
Sheets("Sheet1").Select
TotalRows = ActiveSheet.UsedRange.Rows.Count
TotalCols = ActiveSheet.UsedRange.Columns.Count
For i = 1 To TotalCols
If Cells(1, i).Value = "Test" Then
LookupCol = i
Exit For
End If
Next
For i = 1 To TotalCols
If Cells(1, i).Value = "Values" Then
FormulaCol = i
Range("A2").Activate
Debug.Print "=IF(RC[" & CStr(FormulaCol - LookupCol - 1) & "]=""United States"",1,0)"
ActiveCell.Offset(0, FormulaCol - 1).FormulaR1C1 = "=IF(RC[" & CStr(LookupCol - FormulaCol) & "]=""United States"",1,0)"
Cells(2, FormulaCol).AutoFill Destination:=Range(Cells(2, FormulaCol), Cells(TotalRows, FormulaCol))
With Range(Cells(2, FormulaCol), Cells(TotalRows, FormulaCol))
.Value = .Value
End With
End If
Next
End Sub
回答by ikh
Try using R2C1instead of A2in the function. The reason for this is that you are using Range.FormulaR1C1, which accepts only RC-style references.
尝试在函数中使用R2C1而不是A2。这样做的原因是您正在使用Range.FormulaR1C1，它只接受 RC 样式的引用。
The VBA line which assigns the formula to the cell will then look like this:
将公式分配给单元格的 VBA 行将如下所示：
ActiveCell.Offset(0, FormulaCol - 1).FormulaR1C1 = _
"=IF(RC[" & CStr(LookupCol - FormulaCol) & "]=R2C1,1,0)"
回答by John Bustos
... Replace "]=""United States"",1,0)"with "]=" & Range("A2").Value & ",1,0)"
...替换"]=""United States"",1,0)"为"]=" & Range("A2").Value & ",1,0)"
That should do the trick...
这应该够了吧...
