I have a text file with values in a 2

[Queryman]

I have a text file with values in alphabetical order like this
A
Adam
Andrew
B
Beth
Betsy
C
Charles
D
Dave
Dick
Dom

I need to format just the characters A B C D etc that appears in front of each list below them so that the A B C D are all bolded & underlined and centered. I can find it ( A B C D) using the FINDNEXT command, the problem is VBA is unable to handle missing values. The list does not always contain A to Z, sometimes it does other times it might be missing a few alphabets.

 

[xlbo]

Surely, you can just use ON ERROR GOTO NEXT for this one
If it errors, it'll just continue with the next line... Rgds
~Geoff~

 

[DaleWatson123321]

RIRAY,

I've tested the following and it works...

Dim val As String
Sub Set_Headings()
Application.Goto Reference:="top"
Check_Length
End Sub

Sub Check_Length()
Do
val = ActiveCell.Value
If Len(val) = 0 Then Exit Sub
If Len(val) = 1 Then
Format_Heading
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Sub Format_Heading()
With Selection
.HorizontalAlignment = xlCenter
.Font.Size = 12
.Font.Bold = True
.Font.Underline = xlUnderlineStyleSingle
End With
End Sub


Hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

P.S.

Perhaps I should have added that the reference to "top" is a "range name" assigned to the top-cell, or starting point.

In case you're not used to assigning range names, here's one method:

a) Highlight the cell or-range-of-cells
b) Hold down <Control> and hit <F3>
c) Type the name
d) Hit <Enter>

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Queryman]

Dale, I used your solution, works great, thanks a lot!

 

[Queryman]

Dale Watson spent a lot of his own unpaid time to help me complete this project, it was a highly customized report that is generated using excel and a picture of the data posted to word. It involved a lot of VBA coding and I just want everyone to know how much I appreciate this effort by Dale.


QueryMan