Screening values in cells to create a sentence..

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

Thank you, Nigel. This is perfect!

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

 

[Guest]

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]

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.

 

[Guest]

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]

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.

 

[Guest]

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

 

[Guest]

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]

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.

 

[Guest]

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]

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

 

[Guest]

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