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

[Alex St-Pierre]

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.
--
Alex St-Pierre

[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.*****-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


"Alex St-Pierre" <AlexStPierre@discussions.microsoft.com> wrote in message
news:AEB27F4C-E366-4779-9A30-178F77252B64@microsoft.com...
> 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.
> --
> Alex St-Pierre

[Alex St-Pierre]

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.
--
Alex St-Pierre


"Peter T" wrote:

> 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.*****-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
>
>
> "Alex St-Pierre" <AlexStPierre@discussions.microsoft.com> wrote in message
> news:AEB27F4C-E366-4779-9A30-178F77252B64@microsoft.com...
> > 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.
> > --
> > Alex St-Pierre
>
>
>

[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


"Alex St-Pierre" <AlexStPierre@discussions.microsoft.com> wrote in message
news:7CA4FB01-E8B0-45A1-AF47-1FE4B36C4EFA@microsoft.com...
> 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.
> --
> Alex St-Pierre
>
>
> "Peter T" wrote:
>
> > 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.*****-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
> >
> >
> > "Alex St-Pierre" <AlexStPierre@discussions.microsoft.com> wrote in
message
> > news:AEB27F4C-E366-4779-9A30-178F77252B64@microsoft.com...
> > > 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.
> > > --
> > > Alex St-Pierre
> >
> >
> >