Solved: Find String and Merge Cells

[danesrood]

Dear All

My first posting on this site so please appreciate that I am not that good with vba.

I have a need to look for repeated (quite numerous) occurrences of the months of the year and the text "YTD" that I receive from a main frame as text in the format mmm yyyy and then merge the cell that has the date/YTD in it with the two cells to the right.

The dates are not dates in the Excel way but merely text and every month is only 3 characters long plus the 4 digit year.

So to recap I have Apr 2008 in cell B4 with cells C4 & D4 blankand I want cells B4-D4 to be merged. In addition there will always be a YTD cell with two empty cells to the right that need to be merged.

I hope that I haven't made what I think is quite a simple problem sound to complicated.

Many thanks for any help that you can provide

[MaximS]

can you post a sample workbook?

will be easier to help you.

[danesrood]

Certainly

Sheet attached with dummy data but the original has no formulas just pure text and numbers.
As the year progresses additional months will be included until there are 12 months but there will always be a YTD.

Thank you for taking the time.

Danesrood

[mdmackillop]

Is it necessary to Merge Cells. These can cause problems if further VBA is required. Cente Across Selection can give the same result, without the downside.

[mdmackillop]

No attachment. Use Manage Attachments in the Go Advanced reply section

[danesrood]

mdmackillop

Yes centre across will be fine.

The attachment is there according to Manage Attatchments?

But if you can search for the strings for the month regardless of 2008 or 2009 etc along with YTD and centre across the three cells I am sure that would be fine.

Thank you for your interest.

Danesrood

[MaximS]

try that:

[vba]
Option Explicit
Sub Formating()

Dim Rng, c As Range

Set Rng = ActiveSheet.UsedRange

For Each c In Rng
If IsDate(c.Value) Or c.Value = "YTD" Then
With Range(c, c.Offset(0, 2))
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
End With
End If
Next c
End Sub
[/vba]

[danesrood]

MaximS

That works a treat thank you so much.

But what I can't understand is that you are looking for a date but if I try to add a number to the date and change the format so that I can see the days I get a #Value so it's not a date is it?

In any event it does precisely what I want so again my sincere thanks.

Danesrood

[MaximS]

Excel is automaticaly recognizing if given string matches one of Excel default date formats. And MMM YYYY format is perfectly looking like a date for Excel.

[danesrood]

Seems strange but hey it does what I want.

Again my thanks