How do I detect a string within a range contiguous with my Selection?

Question

I already select a different range of cells throughout the day and check via Vlookup whether account association exists.

If it exists, my vlookup says "true" in columns F and G; if it doesn't my vlookup returns "false"

However, a possible reason why vlookup returns "false" even when account association matches is because of a spacing issue

So, I produced code below which checks to see if the particular columns F and G have any inStr with the word "False".

If any word "False" is detected, a certain trim subprocedure is called to eliminate spacing in my selection.

Detecting "False" string is carried out yet again to make sure that the "False" is now "True" due to the spacing issue being eliminated. If the string "False" still exists after loop iteration, then we have an account mis-association. A message box follows and the code ends

Please, I want to trim my selection only when I detect any string "False" in the adjacent F and G columns ....so my selection, for example, is A10:E14, I only want to detect any "False" strings in F10:G14

Sub myCode()

    Dim iRow As Range, cell As Range
    Set iRow = Range("F2:G100")  '<<<this should be only columns F and G with 
                                    'rows adjacent to my manual selection
    For Each cell In iRow     'for each cell in F and G adjacent to my 
        'selection    
        If InStr(cell.Value, "FALSE") > 0 Then             
            Call Trim_Code
        End If
    Next cell

    For Each cell In iRow
        If InStr(cell.Value, "FALSE") > 0 Then
            MsgBox ("Account Mis-association Found!")
        End If
    Next cell

End Sub

Sub Trim_Code()

      Dim Rng As Range
      Set Rng = Selection

      For Each Cell In Rng    
            Cell.Value = Trim(Cell)    
      Next Cell
End Sub

How do I set my iRow to only adjacent rows of F and G with respect to my selection, and how do I clean up this code to execute faster?

\\\

Solved with kind assistance from PeterT !

Sub Test()

Dim thisWS As Worksheet
Set thisWS = ActiveSheet

Dim Association As Boolean
Association = True

Dim firstRow As Long
Dim lastRow As Long

firstRow = Selection.Cells.Row
lastRow = firstRow + Selection.Cells.Rows.Count - 1

Dim accountChecks As Range

With thisWS
    Set accountChecks = .Range(.Cells(firstRow, 6), .Cells(lastRow, 7))
End With



Dim account As Range

For Each account In accountChecks
    If account = False Then
        Call Trim_Code
    End If


    If account = False Then
        MsgBox "Account Mis-association Found in row " & account.Row & "!"
        Association = False
    End If
Next account


If Association = False Then
    Exit Sub
End If




'proceed to do some crazy code

End Sub

Sub Trim_Code()

  Dim Rng As Range
  Set Rng = Selection

  For Each cell In Rng
        cell.Value = Trim(cell)
  Next cell

End Sub

Your `Trim_Code` routine is only trimming cells in your selected range, NOT in the range of your true/false settings. The example you listed shows where you *select* the range A10:E14, but the true/false values are in F10:G14. In that example, your `Trim_Code` will only trim the cells in A10:E14 because that is your `Selection`. — PeterT, Jul 02 '19 at 13:16
Hello PeterT, thanks for your follow up; that was my intention but I might not have made myself clear that I want to Trim my selection (the values True and False) where just there to call my Trim or else alert the user to the row where False exists still — Nader, Jul 03 '19 at 01:37

score 2 · Accepted Answer · answered Jul 01 '19 at 18:22

2

You have to adjust how you are determining the first and last row of your adjacent selection area AND you have to clearly define how you are defining the range that you want to check. The code example below can help you...

Sub Test()
    Dim thisWS As Worksheet
    Set thisWS = ActiveSheet

    Dim firstRow As Long
    Dim lastRow As Long
    firstRow = Selection.Cells.Row
    lastRow = firstRow + Selection.Cells.Rows.Count - 1

    Dim accountChecks As Range
    With thisWS
        Set accountChecks = .Range(.Cells(firstRow, "F"), .Cells(lastRow, "G"))
    End With

    Trim_Code accountChecks

    Dim account As Range
    For Each account In accountChecks
        If account = False Then
            MsgBox "Account Mis-association Found in row " & account.Row & "!"
        End If
    Next account
End Sub

Sub Trim_Code(ByRef theseCells As Range)
    Dim cell As Range
    For Each cell In theseCells
        cell.Value = Trim(cell)
    Next cell
End Sub

answered Jul 01 '19 at 18:22

PeterT

8,232
1
17
38

I have a follow up question, how do I trim spacing for all cells I paste using Cltr + v? Do I just invoke Trim_Code by assigning that macro using the keys cltr + v? But I don't necessarily want to invoke Trim_Code everytime I paste onto the workbook/worksheet, instead only if "False" is show in columns "F" or "G" . Any thoughts sir? – Nader Jul 01 '19 at 20:54
If you want to invoke this each time you paste on a specific worksheet, create a `Worksheet_Change` macro in the worksheet's module. You can detect if your paste is in column F or G before (possibly) calling `Trim_Code`. If you want to invoke this each you paste on ANY worksheet in the workbook, then create the `Workbook_SheetChange` macro in the `ThisWorkbook` module. – PeterT Jul 01 '19 at 21:01
I actually just found a way where I can detect using similar code if there is any non-True values in the two columns; then my selection would be trimmed – Nader Jul 03 '19 at 01:38

How do I detect a string within a range contiguous with my Selection?

1 Answers1