Hi Ariel
In my original code the two statements where the other way around as shown
here....

xlc = .Cells(xrow, 52).End(xlToLeft).Column
If xlc < 46 then xlc = 46

The first goes from column 52 to column 1 looking for the first value, the
second corrects the value xlc if the first value is before column 46, e.g.
there are no values in the range. Later tests on the length of the values in
the cells will detect a no values situation and display the appropriate
message.

If you have reversed these two lines of code as you show in your latest post
it will not work as expected.

--
Cheers
Nigel



"Ariel" wrote in message
...
Hi Nigel,

Sorry to trouble you with this issue again, but I found a bug in the
following line of code:

If xlc < 46 then xlc = 46
xlc = .Cells(xrow, 52).End(xlToLeft).Column

With this code, the cursor will "jump from column 52 to the left and where
it stops is considered the "last column" which will be evaluated for a
value.
The problem here is if all the cells have values, the xlToLeft statement
will
skip over all those cells with actual values.

To eliminate this problem I think a loop needs to go from cell to cell in
the range (columns 46 through 52) and see if there is a value in the cell.
If
so, it needs to add it to the array you create in the rest of the code?

Can you help me with such a loop to make it work with the code you already
wrote?

Thanks again

Ariel

"Nigel" wrote:

The "," between values is correct - I thought, the "and" does not come
into
play if the last value in column 52 is blank. That is because we
previously
used the last entry to detect the range, which must have a value else it
would not have been detected in the first place!

To fix this, now that you have a fixed range from 46 to 52, you need to
get
the last valid entry in the range, such that xlc is set between 46 (none
or
1 value only, and up to 52 the maximum value.

So the modification will be (replacing the hard code 52 xlc = 52)
with.........

xlc = .Cells(xrow, 52).End(xlToLeft).Column
If xlc < 46 then xlc = 46

Notice how the value 52 is used to set the extent of the test for a blank
column, however if all columns 46 to 52 are blank it may detect a value
less
than 46 so which forces xlc to equal xc.

The comma after each value before the last is set by

xSentence = xSentence & Trim(.Cells(xrow, xc)) & ", "

and the dp (.) after the last value is set by

xSentence = xSentence & "and " & Trim(.Cells(xrow, xc)) & "."

If you need different spacer values change the above as required.

--
Cheers
Nigel



"Ariel" wrote in message
...
Hi Nigel,

The insertion of the line before the msgbox worked!

As for the hard-coding, yes, this needs to be hardcoded to specific
cells.
In this case, col 46 through 52. I hardcoded xlc as 52 and started xc
as
46
(so that it reads: From 46 to xlc. However, now all values get a ","
instead
of "." and the "and" does not come into play...

Here is the current code with your suggested changes:

Sub Sentence()
Dim xlc As Integer, xc As Integer
Dim xSentence As String, xValues As Integer
Dim xrow As Long

'set row to act upon
xrow = 2

With Sheets("raw")
xValues = 0
xlc = 52
xSentence = "You reported that you have "

For xc = 46 To xlc
If Len(Trim(.Cells(xrow, xc))) 0 Then
'use this value
xValues = xValues + 1
If xc = xlc Then
If xValues 1 Then
xSentence = xSentence & "and " & Trim(.Cells(xrow, xc)) &
"."
Else
xSentence = xSentence & Trim(.Cells(xrow, xc)) & "."
End If
Else
xSentence = xSentence & Trim(.Cells(xrow, xc)) & ", "
End If
End If
Next
End With

If xValues = 0 Then
xSentence = "You reported that you have no chronic illness."
End If

MsgBox xSentence
End Sub


Thanks again for your help and patience!

Ariel



"Nigel" wrote:

A problem in specification I think!

If all cells are blank then the first test to get the last column will
be
a
1, but of course this could either be a valid value or a true empty
cell.
The later test check for empty cells and does not append a value, the
counter xVlaues records how many valid entries so you can use this to
add
an
alternative to no entries e.g "No Goods" suggest you add the final
check
after the last End With statement and before the MsgBox as
follows.......

If xValues = 0 then xsentence = "any text you like here"

For a set range you could just define the end column by forcing the
value
of
xlc to a specified column number e.g. 5 (column E)

xlc = 5

put this after or in place of the xlc = .Cells(xrow,
..Columns.Count).End(xlToLeft).Column

This of course would hardcode the value in the macro.





--
Cheers
Nigel



"Ariel" wrote in message
...
Nigel,

I found a problem. If the array is null, the xsentence should return
"no
goods"

Also, is it possible for the user to define the range of columns
rather
than
assessing all columns. So for instance, the range would be A1:E1?

Thanks again. I really appreciate all your help!

Ariel

"Nigel" wrote:

I had applied it to a column, so use this to change it to a row (in
this
case row 1 set the value at the top if you need another row).... I
also
added a check that if the number of items is less then 2 then the
addition
of an 'and' is excluded.

'
Sub Sentence()
Dim xlc As Integer, xc As Integer
Dim xSentence As String, xValues As Integer
Dim xrow as long

'set row to act upon
xrow = 1

With Sheets("Sheet1")
xValues = 0
xlc = .Cells(xrow, .Columns.Count).End(xlToLeft).Column

xSentence = "This store carries the following goods "

For xc = 1 To xlc
If Len(Trim(.Cells(xrow, xc))) 0 Then
'use this value
xValues = xValues + 1
If xc = xlc Then
If xValues 1 Then
xSentence = xSentence & " and " & Trim(.Cells(xrow,
xc))
&
"...."
Else
xSentence = xSentence & Trim(.Cells(xrow, xc)) &
"...."
End If
Else
xSentence = xSentence & Trim(.Cells(xrow, xc)) & ", "
End If
End If
Next
End With
MsgBox xSentence
End Sub


--
Cheers
Nigel



"Ariel" wrote in message
...
Hi Nigel

Thanks for your help. I have two questions:

The code you wrote puts an "and" in front of a single value, so
that
it
may
says:

This store carries the following goods and nails...

Also, can you please show me how the code would be written to
evaluate
across a distinct number of cells in a row. For example:

A1: B1: C1: D1: E1:
paint nails null screws null

In this scenario, the code should evaluate the following array
only
A1:E1.

Thanks again for your help!

Ariel



"Nigel" wrote:

Try this ........ change the sheet name to suit.....

Sub Sentence()
Dim xlr As Long, xr As Long, xSentence As String
With Sheets("Sheet1")
xlr = .Cells(.Rows.Count, 1).End(xlUp).Row

xSentence = "This store carries the following goods "

For xr = 1 To xlr
If Len(Trim(.Cells(xr, 1))) 0 Then
'use this value
If xr < xlr Then
xSentence = xSentence & Trim(.Cells(xr, 1)) & ", "
Else
xSentence = xSentence & " and " & Trim(.Cells(xr, 1)) &
"...."
End If
End If
Next
End With
MsgBox xSentence
End Sub


--
Cheers
Nigel



"Ariel" wrote in message
...
I am trying to write a macro that will insert values from
different
cells
into a sentence. The problem is that not all cells will have a
value
in
them.
Do you have a suggestion that does this? Here's what I mean as
an
example:

Cell A1: paint
Cell B1: nails
Cell C1: null
Cell D1: screws
Cell E1: null


Here is the sentence:

This store carries the following goods: paint, nails, and
screws....

As you can see from the example, I need to skip the cells with
the
null
values.

Any help would be much appreciated.