Excel VBA 使用If语句根据单元格内容自动匹配信函称谓求助

代码问题根源 #

你写的If判断条件存在逻辑错误：

• VBA不支持A = B = C这种连续等式的判断逻辑，你的写法本质是先判断.Application.Selection = ActiveSheet.Range("C4").Value得到布尔值True/False，再用这个布尔值和字符串"mevrouw"比较，永远不成立，所以判断分支完全不会触发
• 你的判断逻辑不需要涉及Selection的值，只需要判断C4单元格的内容即可，Selection此时是你查找到的"heer, mevrouw,"占位符，和C4的称谓没有匹配关系

修正后的可运行代码 #

.Application.Selection.Find.Text = "heer, mevrouw,"
.Application.Selection.Find.Execute
.Application.Selection.Font.Color = vbBlack

' 直接判断C4单元格的内容即可
If ActiveSheet.Range("C4").Value = "mevrouw" Then
    .Application.Selection.TypeText "mevrouw" & Range("D4").Value & ","
ElseIf ActiveSheet.Range("C4").Value = "de heer" Then
    .Application.Selection.TypeText "heer" & Range("D4").Value & ","
End If

.Application.Selection.Move

可选优化方案 #

可以用变量简化代码，避免重复引用单元格，后续维护更方便：

Dim title As String
title = ActiveSheet.Range("C4").Value
Dim userName As String
userName = ActiveSheet.Range("D4").Value

.Application.Selection.Find.Text = "heer, mevrouw,"
.Application.Selection.Find.Execute
.Application.Selection.Font.Color = vbBlack

If title = "mevrouw" Then
    .Application.Selection.TypeText "mevrouw" & userName & ","
ElseIf title = "de heer" Then
    .Application.Selection.TypeText "heer" & userName & ","
End If

.Application.Selection.Move

内容的提问来源于stack exchange，提问作者Amateur