使用“如果单元格包含"，则使用“在VBA Excel中 [英] Using "If cell contains" in VBA excel

问题描述

我正在尝试编写一个宏，如果存在一个带有单词"TOTAL"的单元格，它将在其下面的单元格中输入一个破折号.例如:

I'm trying to write a macro where if there is a cell with the word "TOTAL" then it will input a dash in the cell below it. For example:

在上述情况下，我想在单元格F7中添加一个破折号(注意:可以有任意数量的列，因此它始终是第7行，但不总是F列).

In the case above, I would want a dash in cell F7 (note: there could be any number of columns, so it will always be row 7 but not always column F).

我当前正在使用此代码，但是它无法正常工作，我不知道为什么.

I'm currently using this code, but it's not working and I can't figure out why.

Dim celltxt As String
Range("C6").Select
Selection.End(xlToRight).Select
celltxt = Selection.Text
If InStr(1, celltext, "TOTAL") > 0 Then
Range("C7").Select
Selection.End(xlToRight).Select
Selection.Value = "-"
End If

我们将不胜感激.希望我不会做一些愚蠢的事情.

Help would be appreciated. Hopefully I'm not doing something stupid.

推荐答案

这将循环遍历您定义("RANGE TO SEARCH")的给定范围内的所有单元格，并使用Offset()方法在下面的单元格中添加破折号.作为VBA的最佳做法，永远不要使用Select方法.

This will loop through all cells in a given range that you define ("RANGE TO SEARCH") and add dashes at the cell below using the Offset() method. As a best practice in VBA, you should never use the Select method.

Sub AddDashes()

Dim SrchRng As Range, cel As Range

Set SrchRng = Range("RANGE TO SEARCH")

For Each cel In SrchRng
    If InStr(1, cel.Value, "TOTAL") > 0 Then
        cel.Offset(1, 0).Value = "-"
    End If
Next cel

End Sub

这篇关于使用“如果单元格包含"，则使用“在VBA Excel中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！