Setting string based on conditions
 

Option Explicit
Sub testme()

Dim myMsg As String
Dim myRng As Range
Dim myCell As Range

Set myRng = Worksheets("sheet9999").Range("a1:A7")

myMsg = ""
For Each myCell In myRng.Cells
If myCell.EntireRow.Hidden = True Then
'do nothing
Else
'I put a space between each value
myMsg = myMsg & " " & myCell.Value
End If
Next myCell

If myMsg = "" Then
'nothing found
Else
'get rid of the leading space
myMsg = Mid(myMsg, 2)
End If

MsgBox myMsg

End Sub


"brownti via OfficeKB.com" wrote:

Does anyone have some example code that will set values in a string based on
a condition? For example if row 1 is hidden go on to row 2. If Row 2 is not
hidden set MSGBODY = Range("A2"). If Row 3 is not hidden set MSGBODY = (All
previous conditions and also) Range("A3"). And so on....

I cant figure out how to keep one condition will adding another condition to
the string. What i have is seven rows, some are hidden and some are not.
Depending on if the row is hidden or not i want to put a value from that row
into a string and then use that string in an email message using .Body. Any
ideas?

--
Message posted via http://www.officekb.com

--

Dave Peterson

Setting string based on conditions
 

God I don't read very well at times - Good one Dave


Peter

"Dave Peterson" wrote:

Option Explicit
Sub testme()

Dim myMsg As String
Dim myRng As Range
Dim myCell As Range

Set myRng = Worksheets("sheet9999").Range("a1:A7")

myMsg = ""
For Each myCell In myRng.Cells
If myCell.EntireRow.Hidden = True Then
'do nothing
Else
'I put a space between each value
myMsg = myMsg & " " & myCell.Value
End If
Next myCell

If myMsg = "" Then
'nothing found
Else
'get rid of the leading space
myMsg = Mid(myMsg, 2)
End If

MsgBox myMsg

End Sub


"brownti via OfficeKB.com" wrote:

Does anyone have some example code that will set values in a string based on
a condition? For example if row 1 is hidden go on to row 2. If Row 2 is not
hidden set MSGBODY = Range("A2"). If Row 3 is not hidden set MSGBODY = (All
previous conditions and also) Range("A3"). And so on....

I cant figure out how to keep one condition will adding another condition to
the string. What i have is seven rows, some are hidden and some are not.
Depending on if the row is hidden or not i want to put a value from that row
into a string and then use that string in an email message using .Body. Any
ideas?

--
Message posted via http://www.officekb.com

--

Dave Peterson