Hi, I have a report with 4 lines of title which are centered & span over 12
columns. The first line contains a report number and rundate; separated only
by a single space. I would like to have the report number on the far left
(left justify) and the rundate on the far right (right justify). Any easy
way to add LOTS of spaces between these 2 fields? There are 100+ pages in
the report, therefore, a macro would be helpful. TIA!

Alternatively, if you want the data to display in cells on the worksheet:

Set the leftmost column to .HorizontalAlignment = xlLeft
Set the rightmost column to .HorizontalAlignment = xlRight

'Select the center columns of the report, then
Set the center columns to .HorizontalAlignment = xlCenterAcrossSelection

HTH
Garry

Garry, thx for your ideas. I'm just a recipient of the report files,
therefore, titles are already part of the text. In some cases, I can't left
justify the leftmost column and/or right justify the rightmost column.
However I think I can work off your ideas and try to come up with something.
If not, you might hear from me again :) Thx!

"GS" wrote:

Alternatively, if you want the data to display in cells on the worksheet:

Set the leftmost column to .HorizontalAlignment = xlLeft
Set the rightmost column to .HorizontalAlignment = xlRight

'Select the center columns of the report, then
Set the center columns to .HorizontalAlignment = xlCenterAcrossSelection

HTH
Garry

Here's a different approach. It parses the data and puts it into the leftmost
and rightmost cells respectively. It assumes the number and date are in A1,
the number is followed by a space and the date, and this is the only text in
the cell. If otherwise, post a realistic sample.

Sub ParseNumberAndDate()
Dim sData As String
Dim iPos As Integer
Dim Wks As Worksheet

For Each Wks In ActiveWorkbook.Worksheets

'Get the data
sData = Wks.Range("A1").Value

'Find the position of the delimeter
iPos = InStr(1, sData, " ", vbTextCompare)

'Populate the leftmost cell of the report
With Wks.Cells(1, 1)
.HorizontalAlignment = xlLeft
.NumberFormat = "@"
.Value = Left$(sData, iPos - 1)
End With

'Populate the rightmost cell of the report
With Wks.Cells(1, 12)
.HorizontalAlignment = xlLeft
.NumberFormat = "@"
.Value = Mid$(sData, iPos + 1)
End With
Next Wks

End Sub

You should be able come up with something suitable from this.

Regards,
Garry

"ka2cil" wrote:

Garry, thx for your ideas. I'm just a recipient of the report files,
therefore, titles are already part of the text. In some cases, I can't left
justify the leftmost column and/or right justify the rightmost column.
However I think I can work off your ideas and try to come up with something.
If not, you might hear from me again :) Thx!

"GS" wrote:

Alternatively, if you want the data to display in cells on the worksheet:

Set the leftmost column to .HorizontalAlignment = xlLeft
Set the rightmost column to .HorizontalAlignment = xlRight

'Select the center columns of the report, then
Set the center columns to .HorizontalAlignment = xlCenterAcrossSelection

HTH
Garry

GS - thanks for the tips; they were great base for my coding!

"GS" wrote:

Here's a different approach. It parses the data and puts it into the leftmost
and rightmost cells respectively. It assumes the number and date are in A1,
the number is followed by a space and the date, and this is the only text in
the cell. If otherwise, post a realistic sample.

Sub ParseNumberAndDate()
Dim sData As String
Dim iPos As Integer
Dim Wks As Worksheet

For Each Wks In ActiveWorkbook.Worksheets

'Get the data
sData = Wks.Range("A1").Value

'Find the position of the delimeter
iPos = InStr(1, sData, " ", vbTextCompare)

'Populate the leftmost cell of the report
With Wks.Cells(1, 1)
.HorizontalAlignment = xlLeft
.NumberFormat = "@"
.Value = Left$(sData, iPos - 1)
End With

'Populate the rightmost cell of the report
With Wks.Cells(1, 12)
.HorizontalAlignment = xlLeft
.NumberFormat = "@"
.Value = Mid$(sData, iPos + 1)
End With
Next Wks

End Sub

You should be able come up with something suitable from this.

Regards,
Garry

"ka2cil" wrote:

Garry, thx for your ideas. I'm just a recipient of the report files,
therefore, titles are already part of the text. In some cases, I can't left
justify the leftmost column and/or right justify the rightmost column.
However I think I can work off your ideas and try to come up with something.
If not, you might hear from me again :) Thx!

"GS" wrote:

Alternatively, if you want the data to display in cells on the worksheet:

Set the leftmost column to .HorizontalAlignment = xlLeft
Set the rightmost column to .HorizontalAlignment = xlRight

'Select the center columns of the report, then
Set the center columns to .HorizontalAlignment = xlCenterAcrossSelection

HTH
Garry