Screening values in cells to create a sentence..
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.
|