Excel VBA：如果A2 =“a”，“b”，“c”等等，则B2 =“aa”？ [英] Excel VBA: If A2="a","b","c",etc... then B2="aa"?

问题描述

我正在尝试在A列的每个单元格中获取文本，并根据A列中的文本为B列中的每个单元格指定一个值。例如，我有一个由四个标识的版本列表 - 城市的字母缩写，以及所有这些版本在区域上分配给要生产的不同工厂。因此，假设我有一个AUST，DAFW，HOUS以及更多版本都分配到ARLINGTON的位置。一旦插入所有版本，我最简洁地使用VBA自动化？类似

I'm attempting to take the text in each cell of column A and assign a value to each cell in column B depending on the text in column A. For example, I have a list of versions that are identified by four-letter abbreviations of cities, and all of those versions are regionally assigned to different factories to be produced. So let's say I have an "AUST", "DAFW", "HOUS", and more versions all assigned to the location of "ARLINGTON". How would I most concisely use VBA to automate that once I have all the versions plugged in? Something like

If A2="AUST" Then
B2="ARLINGTON"
ElseIf A2="DAFW" Then
B2="ARLINGTON"

我想这样的事情会起作用，不过我无法相信没有更快捷，更简洁的方式。这有意义吗？我已经把头发拉了大约一个星期，现在试图解决这个问题...感谢您的帮助！

I suppose something like this would work, however I can't believe that there's not a faster and more concise way. Does this make any sense? I've been pulling my hair out for about a week now trying to figure this out... Thanks for any help!

推荐答案

这也可以使用excel公式来完成，但我总是喜欢使用VBA。这应该按你想要的方式工作：

This could be done with excel formulas as well, though I always prefer to use VBA. This should work the way you want :

Sub yourFunk()

    Set wb = ThisWorkbook
    Set ws = wb.Sheets(1)

    arlington = Array("AUST", "DAFW", "HOUS")
    otherLocation = Array("XXXX", "YYYY", "ZZZZ")

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For x = 2 To lastRow

        If stringIsInArray(ws.Cells(x, 1), arlington) Then
            ws.Cells(x, 2) = "ARLINGTON"
        ElseIf stringIsInArray(ws.Cells(x, 1), otherLocation) Then
            ws.Cells(x, 2) = "OTHER LOCATION"
        End If

    Next x


End Sub

Function stringIsInArray(stringToBeFound As String, arr As Variant) As Boolean
    stringIsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

如果您需要我解释代码，请告诉我:)）

If you need me to explain the code, please do let me know :)

这篇关于Excel VBA：如果A2 =“a”，“b”，“c”等等，则B2 =“aa”？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！