View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Greg Wilson[_4_] Greg Wilson[_4_] is offline
external usenet poster
 
Posts: 218
Default VB code is not working like it should

Your code works for at least as far as I can tell. Your
description suggests that the code is not finding the
letters in the named range "rng_Letters". Are you sure
this named range is valid? Also note the underscore
character in the sheet name "Data Entry_". This seems
suspicious although you should get an error message if
this is spelled wrong.

For what it's worth, I put your code on a "low carb" diet
and came up with the following:

Sub findfirstandlast()
Dim Arr(31) As String
Dim rng As Range, cell As Range
Dim i As Integer
Dim txt1 As String, txt2 As String
Dim settext As String

Set rng = Sheets("EXAMPLES").Range("rng_Letters")
settext = " through to "
txt1 = ""
txt2 = ""

For i = 0 To 25
Arr(i) = Chr(i + 65)
Next
For i = 26 To 31
Arr(i) = "A" & Chr(i + 39)
Next

For i = 0 To 31
For Each cell In rng
If cell = Arr(i) Then
txt1 = Arr(i)
Exit For
End If
Next
If txt1 < "" Then Exit For
Next

For i = 31 To 0 Step -1
For Each cell In rng
If cell.Value = Arr(i) Then
txt2 = Arr(i)
Exit For
End If
Next
If txt2 < "" Then Exit For
Next

Sheets("Data Entry_").Select
Range("K33") = txt1 & settext & txt2
End Sub

Regards,
Greg