如果我有下表:
------------------
| X | helloworld |
------------------
| X | random1234 |
------------------
| X | random5678 |
------------------
| X | helloworld |
------------------
| X | random9123 |
------------------
如何将第一个单元格的值设置为左边每次出现helloworld
to 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