热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

将单元格中的文本与excelVBA中的“=text”进行比较-Comparingtextincellwith“=text”inanothercellwithexcelVBA

IamtryingtocomparecellvaluesinaloopwithVBA.TheloopthatIwasusingwasthis我正在尝试将循环中

I am trying to compare cell values in a loop with VBA. The loop that I was using was this

我正在尝试将循环中的单元格值与VBA进行比较。我用的是这个循环。

    For Each r In Rng
If Cells(r.Row, var1) = "string" And Cells(r.Row, var2) = Variable Then
    Cells(r.Row, var3) = "targetstring1"
ElseIf Cells(r.Row, var1) = "string2" And Cells(r.Row, var2) <> Variable Then
    Cells(r.Row, var3) = "targetstring2" 
end if
Next r

I am trying to allow inputting the conditions to an input sheet in the same excel workbook, but I need to make it dynamic. For example, in the input cells I want to put "=string" and "<>XX" where XX is the same value as Variable. This would allow the =/<> to be controlled from the input sheet instead of the code, and allow more flexibility for users to adapt the conditions to their situation.

我试图允许将条件输入到同一个excel工作簿中的输入表中,但我需要使它具有动态性。例如,在输入单元格中,我要输入“=string”和“<>XX”,其中XX与变量值相同。这将允许=/<>从输入表中控制,而不是代码,并且允许用户更灵活地适应他们的情况。

Is this possible? I have tried to use the evaluate() function, but haven't been able to get it to work.

这是可能的吗?我尝试过使用evaluate()函数,但一直无法让它正常工作。

any help is appreciated.

任何帮助都是感激。

Thanks!

谢谢!

1 个解决方案

#1


1  

Test Input:

测试输入:

Test Input

Code:

代码:

Sub Tester()

    Dim rw As Range

    For Each rw In Range("A2:C10").Rows
        If rw.Cells(1) <> "" Then
            rw.Cells(3).Value = Eval(rw.Cells(1), rw.Cells(2))
        End If
    Next rw

End Sub


Function Eval(rngVal As Range, rngCrit As Range)

    Dim addr As String, op As String
    addr = rngVal.Address(False, False)
    op = rngCrit.Value

    Eval = rngVal.Parent.Evaluate(addr & op)
End Function

Result:

结果:

Output

When entering criteria you may need to format your cells as Text or prepend ' to prevent Excel from trying to treat it as a formula

在输入条件时,您可能需要将单元格格式化为文本或前言,以防止Excel试图将其视为公式


推荐阅读
author-avatar
追求生活的垃圾筒
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有