Macro to change cell value

[rosellie]

Hi.... and help!
I'm a newbie to the forum and a newbie to VB but picking it up fairly quickly (the simple stuff, at least). I'm having to write some formatting macros at work and am so far doing ok but there's just one step I just can't seem to get as it involves conditionally changing a cell value and then the cell alignment. I've tried various IF functions but have concluded that the only way to do this must be through the macro... here goes....

I'm using Excel Report Generator to extract data from my database into the Excel template I'm creating the macro for. The sheet that I'm having issues with is because sometimes there will be no data for the period. What my boss wants me to do is set up the macro to include a step where it looks to see if the top row is blank and, if it is, to merge the row (I'm trying to sell 'Center Across Selection' to her) and type 'None' so that the client receiving the report doesn't go looking for data that doesn't exist. Merging has been a nightmare so I've settled for conditionally formatting borders and the whole =IF(..., then "NONE") has left me with a bruise on my forehead from banging it against my desk. Help??


Thanks so much!

[AB33]

A picture is not helpful to test a code. Please attach a sample book. Advance- attachment

[rosellie]

Sorry - hopefully I've attached the right thing this time? I did warn you I'm a newbie!

[AB33]

Rose,
Okay! Which rows/columns you wish to merge and what is the criteria/condition for the merge?

[rosellie]

I need to merge the top row of data (A2:H2) if the whole row is blank.

[AB33]

So, if a whole row in a-h is blank, you want to merge the whole row. If any cell with in A-H si not blank, you do not want to merge that cell. Am I right?

[rosellie]

Yes, it must be the whole row.

[AB33]

Try this one on the attached

Please Login or Register  to view this content.

[rosellie]

That works great... but I should have been clearer when I said the whole row of data, I meant the range A2:H2. This is the range I want to merge. Sorry, for confusing matters by saying row... I feel like I'm learning a new language... which I guess I am! No wonder you had to double check - why would anyone in their right mind merge a whole row of a spreadsheet?!

Also, once the cell has merged, I want it show show a text value: NONE. Is that straightforward?

[AB33]

"why would anyone in their right mind merge a whole row of a spreadsheet?

To give lots of pain and headache for people who want to write a code on merge cells.

I am, like you, is also learnning VBA on this forum. Some people on this forum will not even touch a sample book which has merged cells on it. VBA and merged cells do not see eye to eye.
I have amended the above code as per requested, but I am not sure what you mean by "Text NONE". It is probably an issue of formatting. Could you please explain?

[rosellie]

You are wonderful! That code is perfect! Thank you

The whole thing with 'NONE' is that I want the new merged cell to contain the word 'NONE'. I live without it if that's too much hassle...

[AB33]

Rose,
Amended again. Please above

[rosellie]

Perfect! You have officially made my day! Thanks so much

[AB33]

Rose,
You are welcome!
Glad to be helpful!
Could you please now close this thread as solved. Go in to the top right-hand side of this page, choose, "Thread Tools", you then select, solved from the drop down menu?