CODEWITHSUNDEEP
excelvbaloopstrim
Nader
Nader

Reputation: 67

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

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

Upvotes: 1

Views: 120

Answers (1)

PeterT
PeterT

Reputation: 8557

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

Upvotes: 1

Related Questions