Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2013

    Unanswered: Setting a range in VBA that changes based on a character found.

    Could someone help me? I need help with a procedure to set a range (test_text) for a portion of text contained within multiple adjacent vertical cells. I have a series of an indefinite number of adjacent vertical cells with a special character at the end of the last cell at the end of the text to signal the end of the range. Text are sentences of a paragraph lets say with each cell containing one or more sentences. Paragraph (range I want to set) can be anywhere from one cell to many cells in number. Special character ₱ is the very last character of the last cell of the range. Please find attached sample file. Thank you.
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2002
    Bay Area
    I have Excel 2003 and here is my idea on how this can be solved.
    You have a symbol that I can't find on the ASCII chart, so I copied
    it to a separate worksheet to be able to assign it to a variable and
    do a search.
    A workbook is attached. I assigned Ctrl+e to run the macro.
    Sub MyMacro()
    Dim specSymbol As String
    Dim lastRow As Long
    Dim rngUsed As Range, rngFound As Range
        specSymbol = Sheets("PSymbol").Cells(1, 1).Value
        If specSymbol = "" Then
            MsgBox "Copy the special symbol to PSymbol sheet, Cell A1 before using this macro."
            Exit Sub
        End If
        Application.ScreenUpdating = False
        lastRow = Range("A65536").End(xlUp).Row
        Set rngUsed = Range("A1:A" & lastRow)
        On Error Resume Next
        Set rngFound = Cells.Find(What:=specSymbol, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        On Error GoTo 0
        If rngFound Is Nothing Then
            MsgBox "Unable to find the special symbol " & specSymbol
            GoTo Exit_MyMacro
            Range("A1:A" & rngFound.Row).Select
        End If
        Set rngUsed = Nothing
        Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files
    Last edited by JerryDal; 04-02-13 at 02:12. Reason: changed 'Sheet2' to 'PSymbol sheet'

Posting Permissions

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