How to know if a text is displayed on an empty cell

[Guest]

Hi !
I have a text in cells(1,1) that go on cells(1,2) and cells(1,3) Is there a
function that allow me to count how many columns are used by cells(1,1).
I could do something like:

Dim UsedCol as Interger
If(Cells(1,1)<> "")
UsedCol = 1
If(Cells(1,2)= "" And "Text go on the cells") Then
UsedCol = UsedCol + 1
If(Cells(1,3)= "" And "Text go on the cells") Then
UsedCol = UsedCol + 1
End If
End If
Msgbox("Number of column used: " & UsedCol)
End If

Thank you.

 

[Peter T]

Hi Alex,

If I understand your question you want to measure the length of text in a
cell and work out how many cells to the right it extends over. To calculate
is very difficult unless the font is non proportional (eg courier) but still
need to cater for bold, italic, different character formats etc.

The simple way is to copy the cell to a dummy sheet, autofit, compare the
width with the original cell and its neighbours. If that's not viable then
you might like to try some of the examples here

http://www.dicks-blog.com/archives/2005/05/16/1131/

There's one at the bottom I posted with some difficulty, and spread over
three attempts. If you can't put that together you I can send you the
original.

Regards,
Peter T
pmbthornton gmail com

 

[Guest]

Hi Peter,
I will appreciate if you can send me the macro with all parts together.

The reason why I need to know that is because when I copy table from excel
to word, Word merge the cells together. I need to know that if excel
cells(1,1) and cells(1,2) have the same text, Word will merge these and
cells(1,2) in word will correspond to cells(1,3) in excel. For now, it works
only if the cells are manually merged in excel (I make it correspond using
MergeArea.Count)
Thanks.

 

[Peter T]

Hi Alex,

I will appreciate if you can send me the macro with all parts together.

You'll need to post your address or contact me off-line (see below and add
the obvious at & dot). On line we are both "at-discussions" so replying
there won't get anywhere (I haven't heard from you off-line so maybe you
tried to reply there).

Must admit I couldn't re-assemble "FillLongText" myself but others have told
me they didn't have a problem. For your purposes I think you will only need
to change

'If c Then cel.Resize(1, c).Interior.ColorIndex = 36
to
If c Then cel.Resize(1, c).Merge

Also of course replace the clear colour format stuff with un-merge.

I've since made a couple of minor amendments to the macro.

Regards,
Peter T
pmbthornton gmail com