VBA: Search Used Range For Values, Return Triggered Result OUTSIDE of Used Range.

[SteveOranjinSteve]

Hello,

Hope you're well. I have been working on some code that I would like someone to review. This code currently prints a triggered result, one cell over, to the right, when it encounters "TOTAL" within a string. I would like it to print out the same value, except outside of the range it is searching, in the next subsequent column over.

Sub AddDashes()

Dim SrchRng As Range, cel As Range

Set SrchRng = ActiveSheet.UsedRange

For Each cel In SrchRng
    'InStr returns a 'variant'(long) specifying the position of the first occurrence of one string within another.
    'it contains four arguments.
        ' Instr([Numeric Expression, that sets the starting position for each search.],(String1) Required: String expression being search for.,(String2)Required: String expression being sought.)
    If InStr(1, cel.Value, "TOTAL") > 0 Then
        cel.Offset(0, 1).Value = "TOTAL"
    End If
Next cel
End Sub

I've tried substituting "cel" for "SrchRng", but that just then replaces ALL the values with "Total", and that certainly isn't what I want.



Start

fadfasd Total

RESULT

fadfasd TOTAL	TOTAL
fdfasdfasd
dfadfa TOTAL	TOTAL

 

[Fluff]

How about

Cells(cel.Row, SrchRng.Columns.Count + 1).Value = "TOTAL"

 

[SteveOranjinSteve]

Perfect.

Can you describe what exactly that code does please?

 

[Fluff]

It takes the row of the range variable "cel" & the number of columns in the "SrchRng" +1 & uses them as the row/column index in Cells