时间戳

首页 > 解决方案 > 如何在 If 语句中进行近似匹配？

excel - 如何在 If 语句中进行近似匹配？

问题描述

我创建了一个代码；但是，它可能无法正常工作。If 语句仅适用于绝对值。假设有人以小写形式输入了 ItemType，If 语句将无法获取该值，因此不会更新相应的工作表。我想消除工作簿中可能出现的错误或遗漏。

Sub test()

' ----------------------------------------------------------------- Data
Dim ws0 As Worksheet: Set ws0 = ThisWorkbook.Sheets("Data")
' ----------------------------------------------------------------- Mfg FG
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("ABCX Mfg FG")
' ----------------------------------------------------------------- Mfg RAW
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("ABCX Mfg RAW")


Dim ItemNumber As String
Dim ItemType As String
Dim Issues As String
Dim InventoryValue As String

ItemNumber = InputBox("Please enter Item Number", "Item Number", "Type here")
ItemType = InputBox("Please enter Item Type", "Item Type", "Type here")
Issues = InputBox("Please enter Number of Issues", "Issues", "Type here")
InventoryValue = InputBox("Please enter Inventory Value", "Inventory Value", "Type here")

'-------------------- Data
NextRow = ws0.Cells(ws0.Rows.Count, "A").End(xlUp).Offset(1, 0).Row

 ws0.Range("A" & NextRow).Value = ItemNumber
 ws0.Range("F" & NextRow).Value = ItemType
 ws0.Range("H" & NextRow).Value = Issues
 ws0.Range("I" & NextRow).Value = InventoryValue

 ws0.Range("A" & NextRow - 1 & ":I" & NextRow - 1).Copy
 ws0.Range("A" & NextRow & ":I" & NextRow).PasteSpecial Paste:=xlPasteFormats
 Application.CutCopyMode = False
 Application.ScreenUpdating = True

'----------------- If statement
If ItemType = "Mfg FG" Then
ws1.Activate
Range("A13").Activate
 ElseIf ItemType = "Mfg RAW" Then
 ws2.Activate
 Range("A13").Activate

End If

'-------------------- Loop
Do

If ActiveCell.Value = "" Then Exit Do
ActiveCell.Offset(1, 0).Activate

Loop

ActiveCell.Value = ItemNumber
ActiveCell.Offset(0, 1).Value = Issues
ActiveCell.Offset(0, 2).Value = InventoryValue



 Application.CutCopyMode = False
 Application.ScreenUpdating = True

End Sub

标签： excelvba

解决方案

将您的变量更改为大写 UPPER(ItemType) UPPER(Issues) UPPER(InventoryValue)

并将您的比较值设为大写，例如：UPPER("MFG FG")。那么他们用什么方式输入就无关紧要了。前面的评论者也在谈论关于 ACTIVECELL 的福音真理。使用它是在解决问题！

推荐阅读