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€¦

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.

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.

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.

Thank you, Nigel. This is perfect!

"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.

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.

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.

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.

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.

Nigel, you are a serious genious! Thank you sooooo much. This is bomb-proof
now.

"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.

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.

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.

No I just wrote it wrong in the blurb here. The issue still stands: the code
needs a loop to determine if there is a value in each cell within that range
of cells, rather than using the xlLeft function.

Thanks

Ariel

"Nigel" wrote:

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

But there is already a loop to read from column 46 to xlc; the code we are
talking about determines how high the loop goes by setting xlc

For each column read the code then adds non-blank cells to the output
string.

It works for me here, so I do not understand why you have a problem? If you
just want to read from column 46 to 52 then set xlc to 52 and remove the
code that sets xlc ! The loop is there already as I said.

In fact the reason the code was included is that the original post did not
specify a fixed range!

xlc = .Cells(xrow, 52).End(xlToLeft).Column < remove this row
If xlc < 46 then xlc = 46 < change this to this
xlc = 52

Hope it works for you now

--
Cheers
Nigel



"Ariel" wrote in message
...
No I just wrote it wrong in the blurb here. The issue still stands: the
code
needs a loop to determine if there is a value in each cell within that
range
of cells, rather than using the xlLeft function.

Thanks

Ariel

"Nigel" wrote:

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

That fixed the issue with the final column value being missed. However, now
instead of a period, the last column value (52) has a comma placed in the
sentence.

Thanks Nigel

"Nigel" wrote:

But there is already a loop to read from column 46 to xlc; the code we are
talking about determines how high the loop goes by setting xlc

For each column read the code then adds non-blank cells to the output
string.

It works for me here, so I do not understand why you have a problem? If you
just want to read from column 46 to 52 then set xlc to 52 and remove the
code that sets xlc ! The loop is there already as I said.

In fact the reason the code was included is that the original post did not
specify a fixed range!

xlc = .Cells(xrow, 52).End(xlToLeft).Column < remove this row
If xlc < 46 then xlc = 46 < change this to this
xlc = 52

Hope it works for you now

--
Cheers
Nigel



"Ariel" wrote in message
...
No I just wrote it wrong in the blurb here. The issue still stands: the
code
needs a loop to determine if there is a value in each cell within that
range
of cells, rather than using the xlLeft function.

Thanks

Ariel

"Nigel" wrote:

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