英文:
VBA Excel clear contents after parentheses of a formula
问题
Worksheets(1).Columns("J:K").Select
For Each cell In Selection
If cell.Interior.Color = vbYellow Then
cell.Value = Left(cell.Formula, InStrRev(cell.Formula, ")") - 1)
cell.Interior.Color = RGB(0, 0, 255)
End If
Next
英文:
For a small procedure within a larger VBA Excel script I have the below code:
Worksheets(1).Columns("J:K").Select
For Each cell In Selection
If cell.Interior.Color = vbYellow Then
cell.Clear
End If
Next
The code searches for all yellow cells within columns J and K and clears the contents.
However I would like to have the code clear just a part of the contents of these yellow cells. These cells contain a formula and I would like to clean everything after the last parentheses of these formula's, so the "-1" in this case.
=IFERROR(VLOOKUP(D147;'https://.....VAS pivot'!$B$2:$E$728;2;0);0)-1
When cleared I would like to color these cells blue.
Can someone help me with this? Thank you in advance.
答案1
得分: 2
使用 InStrRev 函数在黄色单元格公式中从右侧搜索括号。然后在该位置截断公式：
Worksheets(1).Columns("J:K").Select
For Each cell In Selection
If cell.Interior.Color = vbYellow Then
Dim pos As Integer
pos = InStrRev(cell.Formula, ")")
cell.Formula = Left(cell.Formula, pos)
End If
Next
英文:
Use InStrRev to search for the paranthesis in the yellow cells formula from the right side. Then just cut it off at that position:
Worksheets(1).Columns("J:K").Select
For Each cell In Selection
If cell.Interior.Color = vbYellow Then
Dim pos As Integer
pos = InStrRev(cell.Formula, ")")
cell.Formula = Left(cell.Formula, pos)
End If
Next
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论