Results 1 to 7 of 7

Macro not finding finding selected characters in strings

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365 Version 2501 64-bit
    Posts
    1,491

    Macro not finding finding selected characters in strings

    Following code should look at every cell in sheet 1 used range, move any rows where a cell contains one of these four characters: ' or ; or - or ! and delete the blank row in sheet 1.

    It's cycling through the cells correctly, but not finding the characters:

    Option Explicit
    Dim sht As Worksheet, sht2 As Worksheet
    Dim a As Long, j As Long, x As Long
    Dim sCharOK As String, s As String
    Dim r As Range, rc As Range
    
    Sub Characters()
    
    Set sht = Sheet1
    Set sht2 = Sheet2
    
        With sht2
    'Find the first free row on the Database containing data and set to memory
        a = sht2.Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row + 1
        If a < 2 Then a = 2
        
    sht.Activate
    
    'Set the range to check
    Set r = sht.UsedRange
    
    'Set the characters to look for:
    sCharOK = " ' ; - !"
    
    'Cycle through the range and move any rows containing the characters to next free row on sheet 2
    For Each rc In r
        s = rc.Value
        For j = 1 To Len(s)
        If InStr(s, sCharOK) > 0 Then
            x = ActiveCell.Row
            Rows(ActiveCell.Row).Cut
            sht2.Range("A" & a).Insert
    'Reset the next free row and delete empty row in Sheet 1
            a = a + 1
            sht.Rows(x).Delete
            End If
        Next j
        
    Next rc
    
    End With
    
    End Sub
    Any guidance, suggestions or solutions welcome as ever

    Ochimus
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Finding two sub-strings within a string
    By pablowilks in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-26-2015, 03:05 PM
  2. Finding two sub-strings within a string
    By pablowilks in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-22-2015, 10:00 AM
  3. Replies: 9
    Last Post: 12-21-2012, 04:18 AM
  4. Finding exact words i strings
    By Flabbergaster in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-16-2012, 01:48 PM
  5. Error 91 while finding strings
    By spammy2 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-14-2012, 01:33 PM
  6. Macro for finding out all non-keyboard characters and symbols in a sheet
    By Merry in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-22-2008, 04:26 PM
  7. Finding differences in strings
    By tchezick in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-22-2005, 11:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1