Determining first and last areas on multiple cells

  • Thread starter Thread starter Bob Reynolds
  • Start date Start date
B

Bob Reynolds

I have 30 worksheets that use cell $H$50 to indicate the following "Example:
(applicable letter)" Example: B.
The example is determined by the use of one additional sheet, so Example: B
could be either "Example: A" or "Example: B".

These will continue through the alphabet up to 30 letters.

My problem is that I have a cell that I have to enter the beginning letter
of the example and the ending letter of the example in another cell to be in
text like:
"Example A through G". Is there a way to extract the first letter and the
last letter, since both are variable and put them in that statement? Any
help would be appreciated.
Thanks
BOB REYNOLDS
 
Hi Bob
These will continue through the alphabet up to 30 letters.
Click to expand...

bit confused here ... alphabet only has 26 letters, what are you using for
the last 4?

Cheers
JulieD
 
Hi Bob

sorry me again
how are your sheets named ... could you extract the first & last letter by
using the sheet name and then the CONCATENATE()
to put them into the required sentance.

Cheers
JulieD
 
Hi Bob
if A1 stores your beginning letter and B1the ending letter use the
followinf formula
="Example " & A1 & " through " & B1
 
Sheet names are "EE 01" etc through "EE 30". Within the worksheet is a cell
with this formula

=IF('Data Entry_'!$O$19<>"",IF('Merge Data_'!T2="listed on Exhibit A (fully
incorporated herein by reference) ","Exhibit: H","Exhibit: G"),"")

In this case the return is "Exhibit: H".

After the 26th letter it goes to AA, AB, AC and AD.
I have used the right function and have a Column of cells with just the
letters. If a particular worksheet doesn't have an example there is a blank
cell as you can see by the formula

I need to look at the column and lets say it shows A,B,C,D,E,F,G,H,I as
examples. This would be listed in a column down. Then I need to go to a
seperate cell and do the following: Insert the first letter (A) then
"through" and the last letter (I). If they were constant I could handle it
but it varies as I could only have two letters or thirty letters. The end
result show show

A through I as text.

Thanks
Bob
 
Hi Bob

so basically you need to be able to identify the first cell in a range and
then last cell in the range - but there can be blanks in the range (and at
the beginning or end) - and then you need to concatenate them into a string

Personally i can't see that you can do it with a function, i would think you
needed to use VBA .. are you happy to have a VBA solution?

Cheers
JulieD
 
VBA works for me, but don't know that one either. The blanks will only be at
the bottom of the column. There will always be an entry in the first cell
and second cell and the rest may or may not be blank. If you can help
withVBA I'll insert it away.

Thanks
BOB
 
Also one final small issue. Once these are inserted in the cell as I
mentioned. I need VBA or something that will return the next letter into
another cell.
This document is a legal document to be filed with the court and I'm using
excel as the mail merge data source. Once the "A through I" is returned in
the one cell I need to go down one cell and have it automatically insert the
letter "J". Is that too much more difficult????
Thanks again'
BOB
 
Hi Bob

this should work when pasted into a module
-----
Sub findfirstandlast()

Dim rng As Range
Dim myarray(2) As String
Dim settext As String

settext = """ through to """


Set rng = Sheets("Sheet1").Range("rng_Letters")
For Each cell In rng
If cell = "A" Then
myarray(1) = "A"
Exit For
ElseIf cell = "B" Then
myarray(1) = "B"
Exit For
ElseIf ****you can do the rest of the typing here - i'll guess
you'll have to go right down the alphabet

End If
Next

For Each cell In rng
If cell = "AD" Then
myarray(2) = "AD"
ElseIf cell = "AC" Then
myarray(2) = "AC"
ElseIF ****you can do the rest of the typing here - i'll guess
you'll have to go right up the alphabet

End If
Next

Range("A1").FormulaR1C1 = "=Concatenate(""" & myarray(1) & """," &
settext & " ,""" & myarray(2) & """)"

End Sub

------
 
amazing what you learn on these boards :)
both his name & email address lead me to assume he was american though :)


Cheers
JulieD
 
He is without doubt at least using an American ISP.. <g>
To get Swedish characters hold down alt while typing 134,
repeat but use 132 and finally 148..
 
thanks ... not sure i have a use for this right away but you never know :)
.... Australia is a long way from Sweden
 
When I went there it didn't take that long. About 24 hours.
OTOH it too me 3 days to get from Melbourne to Cairns (I had
to see the crocs that had eaten a Yank the same week)
Except for a meeting with a Portuguese Man of War I had
a splendid time <g>
 
glad you enjoyed your time over here ... i'm actually on the other side of
the country ... so even Cairns is a long way away :)

one of my regrets is that when i was in europe i didn't get up to the
scandenavian countries ... maybe one day

Cheers
JulieD
 
Back
Top