将公式给出的单元格值声明为字符串-DeclareCellvaluegivenbyformulaasstring

Hi there I have a formula that displays a value in a cell, however I cannot declare the value of that cell as string in a VBA code as it's not a "real" value but a dynamic one based on the formula. How would I go about using the value as a string? Is there any way to convert a result given by a formula to string via VBA?

嗨,我有一个公式,在单元格中显示一个值,但是我无法在VBA代码中将该单元格的值声明为字符串,因为它不是“真实”值,而是基于公式的动态值。我如何将该值用作字符串?有没有办法通过VBA将公式给出的结果转换为字符串?

This is the formula in cell I1: =IFERROR(VLOOKUP(J3,REVIEWERS!A:C, 3, 0),"")

这是单元格I1中的公式:= IFERROR(VLOOKUP(J3,REVIEWERS!A:C,3,0),“”)

I am trying to declare the value in I1 as string with the following code:

我试图使用以下代码将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

3 个解决方案

ReviewerID = list.Range("I1").Text

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

=TEXT(IFERROR(VLOOKUP(J3,REVIEWERS!A:C, 3, 0),""),"General")