Hard return in code only for cells that have va

[ChristineJ]

This code below uses cell values to add comments to a comment box. I am trying to avoid having a hard return entered (& vbCrLf) for any cell in columns 79 to 85 that is blank. See the three lines of code I tried to add to accomplish this, but it is not working (generates an error that it wants a "For").

1. Can this be changed so that there is no hard return if the cell is blank?
2. On another note, i =79 to 85, a contiguous range of columns. Is there a way to look at 79 to 81 and 84 to 85, for example (non-contiguous)?

Thanks! C

Sub AddCommentBox(rowNumber)  ' will be 10 through 16

Dim c As Range
Dim commentText As String
Dim i As Integer

For i = 79 To 85
 '   If IsEmpty(Cells(rowNumber, i)) Then       THESE THREE LINES ARE WHAT I AM TRYING TO ADD
'Next i                                                            THESE THREE LINES ARE WHAT I AM TRYING TO ADD
  '  Else                                                            THESE THREE LINES ARE WHAT I AM TRYING TO ADD
    If IsNumeric(Cells(rowNumber, i)) Then
        commentText = commentText & Format(Cells(rowNumber, i), "#,###")
    Else
        commentText = commentText & Cells(rowNumber, i)
    End If
    commentText = commentText & vbCrLf        'I don't want a hard return if the cell is blank
    Next i

Set c = Cells(rowNumber, 16)

c.AddComment
c.Comment.Text Text:= commentText
c.Comment.Shape.TextFrame.AutoSize = True

End Sub

Sub JUNK()
Call AddCommentBox(10)
End Sub

 

[davesexcel]

Use a variable to see if the cell is blank, if blank then add a blank, if not blank the add vbCrLf

For i = 79 To 85
'-----------------------------'
x=iif((rowNumber, i)="","",vbCrLf)
'------------------------------------'


    If IsNumeric(Cells(rowNumber, i)) Then
        commentText = commentText & Format(Cells(rowNumber, i), "#,###")
    Else
        commentText = commentText & Cells(rowNumber, i)
    End If
    commentText = commentText & x        'I don't want a hard return if the cell is blank
    Next i

 

[ChristineJ]

Use a variable to see if the cell is blank, if blank then add a blank, if not blank the add vbCrLf

For i = 79 To 85
'-----------------------------'
x=iif((rowNumber, i)="","",vbCrLf)
'------------------------------------'


    If IsNumeric(Cells(rowNumber, i)) Then
        commentText = commentText & Format(Cells(rowNumber, i), "#,###")
    Else
        commentText = commentText & Cells(rowNumber, i)
    End If
    commentText = commentText & x        'I don't want a hard return if the cell is blank
    Next i

This makes perfect sense, but I am getting a compile error "Expected )" for the x=iif((rowNumber, i)="","",vbCrLf)

Any idea why? Thanks! C

 

[davesexcel]

Oh ya, missing the word cells(

 

[ChristineJ]

Oh ya, missing the word cells(

Where would I put that? Sorry - new at this....CJ

 

[ChristineJ]

Oh ya, missing the word cells(

Disregard my previous question. I added "Cells(" where it should be. Works perfectly! Thanks so much. CJ