Excel VBA - Searching long words

  • Thread starter Thread starter ozcank
  • Start date Start date
O

ozcank

Hi all,

Just a quick query. I have a macro, which searches through column B o
a spreadhseet and creates new sheets according to the content of th
cell. However, it doesn't accept any cell, which contains more than 3
characters. What should I use in my declaration so that it accepts lon
words?

Cheer
 
Well, I'm guessing, but 31 characters is the maximum length of the sheet
name, so that may be the thing blocking you.
 
Good thinking, I neglected that small factor. I have the following code
which selects the name of the cell:

Private Sub getNames()
Dim intRow As Integer, wsMain As Worksheet
Dim strReadName As String

Set wsMain = ActiveWorkbook.Worksheets("ufioverdue")
intIdx = 0
intRow = 2
With wsMain
Do
strReadName = .Cells(intRow, 2)
strNames(intIdx) = strReadName

Do
intRow = intRow + 1
Loop Until strReadName <> .Cells(intRow, 2)

intIdx = intIdx + 1

Loop While .Cells(intRow, 2) <> ""
End With
intIdx = intIdx - 1

End Sub

What would I have to alter so that it would only select the first 3
characters of a cell? ny ideas?

Cheer
 
Try changing

..Cells(intRow, 2)

with

Left$(.Cells(intRow, 2).Value, 31)
 
sStr = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
sStr1 = Left(sStr,31)
? sStr
ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz
? sStr1
ABCDEFGHIJKLMNOPQRSTUVWXYZabcde
 
Back
Top