如何修改此脚本以适应不同的情况? [英] How to modify this script to account for different case?

问题描述

我编写了一个 VBA 脚本，它在一个列中搜索一个字符串并在另一列中返回匹配项.重点是将商品编号与其 SKU 的产品图片相匹配.问题是如果项目编号和图像的大小写不同，它就会失败.脚本如下:

I wrote a VBA script which searches for a string in one column and returns the match in another column. The point is match an item number to it's product image by SKU. The problem is that it fails if the case of the item number and the image have different case. The script is below:

Sub Test()
    Dim NA As Long, NC As Long, v As String, I As Long, J As Long
    Dim v2 As String
    NA = Cells(Rows.Count, "A").End(xlUp).Row
    NC = Cells(Rows.Count, "C").End(xlUp).Row
    For I = 2 To NA
        v = Cells(I, "A").Value
        v2 = ""
        For J = 2 To NC
            If InStr(Cells(J, "C").Value, v) > 0 Then
                v2 = v2 & ";" & Cells(J, "C").Value
            End If
        Next J
        Cells(I, "A").Offset(0, 1).Value = Mid(v2,2)
    Next I
End Sub

我如何应对不同的情况?有这个方法吗?

How can I account for different case? Is there a method for this?

推荐答案

很简单，转小写即可:

Sub Test()
    Dim NA As Long, NC As Long, v As String, I As Long, J As Long
    Dim v2 As String
    NA = Cells(Rows.Count, "A").End(xlUp).Row
    NC = Cells(Rows.Count, "C").End(xlUp).Row
    For I = 2 To NA
        v = LCase(Cells(I, "A").Value)
        v2 = ""
        For J = 2 To NC
            If InStr(LCase(Cells(J, "C").Value), v) > 0 Then
                v2 = v2 & ";" & Cells(J, "C").Value
            End If
        Next J
        Cells(I, "A").Offset(0, 1).Value = Mid(v2,2)
    Next I
End Sub

我刚刚获取了您的代码示例，并在 v 和 Cells(J, "C").Value 上将强制转换为小写.

I just took your code sample and put casts to lower case on v and Cells(J, "C").Value.

我没有测试过这个，所以我不能保证完美的功能.

I didn't test this, so I can't guarantee flawless functionality.

你也可以改变你的模块(在顶部)

You can also alter your module (on top) with

选项比较文本

这将完全关闭区分大小写(所以你不需要LCase(..))

That will shut down case sensitivity completely (so you wouldn't need LCase(..))

这篇关于如何修改此脚本以适应不同的情况?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！