Posted By: Anonymous
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:
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.
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
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