更改与特定值匹配的单元格的相邻单元格

更改与特定值匹配的单元格的相邻单元格

如果我有下表:

------------------
| X | helloworld |
------------------
| X | random1234 |
------------------
| X | random5678 |
------------------
| X | helloworld |
------------------
| X | random9123 |
------------------

如何将第一个单元格的值设置为左边每次出现helloworldto Y

经过处理后,我期望上面的例子是:

------------------
| Y | helloworld |
------------------
| X | random1234 |
------------------
| X | random5678 |
------------------
| Y | helloworld |
------------------
| X | random9123 |
------------------

为了澄清起见,我的电子表格包含数千个这样的事件,我正在寻找一个伪代码中的批量操作:

for every row:
    if column_B = 'helloworld':
        column_A = 'X'
    if column_B = 'random':
        column_A = ...

答案1

这个 VBa 可以做到。

没有撤消的选项,因此请先备份!

Sub WalkThePlank()

Dim updateColumn As String
updateColumn = "A"           'update this ye filthy seadog if needed! This be the first column

Dim contentColumn As String
contentColumn = "B"         'aye, scrub the deck and update if the "helloworld" isn't in column B

Dim startRow As Integer
startRow = 1                  'enter the start row or be fed to the sharks  

Do While (Range(contentColumn & startRow).Value <> "")

    Dim val As String
    val = Range(contentColumn & startRow).Value

    Select Case val

    Case "helloworld"
        Range(updateColumn & startRow).Value = "Y"

    Case Else
        Range(updateColumn & startRow).Value = "X" ' This is the "default value

    End Select        

    startRow = startRow + 1

Loop    

End Sub

要添加其他情况,例如搜索词为 Goodbyeworld,请将代码更新为

    Select Case val

    Case "helloworld"
        Range(updateColumn & startRow).Value = "Y"

    Case "Goodbyeworld" ' CASE SENSITIVE!!!!
        Range(updateColumn & startRow).Value = "A"

    Case Else
        Range(updateColumn & startRow).Value = "X" ' This is the "default value

    End Select        

如何在 MS Office 中添加 VBA?


在此处输入图片描述


在此处输入图片描述

相关内容