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.
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.
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."
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, _
On Error GoTo 0
If rngFound Is Nothing Then
MsgBox "Unable to find the special symbol " & specSymbol
Range("A1:A" & rngFound.Row).Select
Set rngUsed = Nothing
Application.ScreenUpdating = True
Last edited by JerryDal; 04-02-13 at 02:12.
Reason: changed 'Sheet2' to 'PSymbol sheet'