您好,我有一个在单元格中显示值的公式,但是我无法在 VBA 代码中将该单元格的值声明为字符串,因为它不是"真实"值,而是基于公式的动态值。我将如何将该值用作字符串?有没有办法通过VBA将公式给出的结果转换为字符串?
这是单元格I1中的公式:=IFERROR(VLOOKUP(J3,REVIEWERS!A:C, 3, 0),"")
我尝试使用以下代码将 I1 中的值声明为字符串:
Public list As Worksheet
Public bsawt as worksheet
Sub BSAW_Export()
Dim ReviewerID As String
Set list = Sheets("LIST")
Set bsawt = Sheets("BSAW_TABLE")
ReviewerID = list.Range("I1").Value
lastrow = list.Cells(Rows.Count, "E").End(xlUp).Row
For x = 2 To lastrow
If bsawt.Range("E" & x).Value <> " error" Then bsawt.Range("F" & x).Value = ReviewerID
Next x
End Sub
最简单的方法是改用 .Text
属性
ReviewerID = list.Range("I1").Text
试试CStr
函数?CStr 函数
Public list As Worksheet
Public bsawt As Worksheet
Sub BSAW_Export()
Dim ReviewerID As String
Set list = Sheets("LIST")
Set bsawt = Sheets("BSAW_TABLE")
ReviewerID = CStr(list.Range("I1").Value)
lastrow = list.Cells(Rows.Count, "E").End(xlUp).Row
For x = 2 To lastrow
If bsawt.Range("E" & x).Value <> " error" Then bsawt.Range("F" & x).Value = ReviewerID
Next x
End Sub
没有 VBA
将公式输出转换为文本的常用方法是:
=TEXT(IFERROR(VLOOKUP(J3,REVIEWERS!A:C, 3, 0),""),"General")
如果您有特定的文本格式,如日期或货币,那么只需替换"常规">