Need help cleaning up this ugly if/elseif block.

[Drumwaves]

Hello friends,

I spent a while searching and googling a solution with no resolution.

Tried creating an array, but my array skills are lack luster.

Basically, I'd like a suggestion of an easier/cleaner block of code to accomplish the same thing. This code functions properly but seems very inefficient for my taste. I really only care about the top if block. There is other code in this sub, but didn't think it was important to include.


Dim DiagList as String
Dim HCC_WS as Worksheet
Dim CurRow as Long


If Me.tb_Diag1 <> "" And _
Me.tb_Diag2 <> "" And _
Me.tb_Diag3 <> "" And _
Me.tb_Diag4 <> "" Then

DiagList = Me.tb_Diag1 & Chr(10) & Me.tb_Diag2 & Chr(10) & Me.tb_Diag3 & Chr(10) & Me.tb_Diag4
ElseIf Me.tb_Diag1 <> "" And _
Me.tb_Diag2 <> "" And _
Me.tb_Diag3 <> "" And _
Me.tb_Diag4 = "" Then
DiagList = Me.tb_Diag1 & Chr(10) & Me.tb_Diag2 & Chr(10) & Me.tb_Diag3

ElseIf Me.tb_Diag1 <> "" And _
Me.tb_Diag2 <> "" And _
Me.tb_Diag3 = "" And _
Me.tb_Diag4 = "" Then
DiagList = Me.tb_Diag1 & Chr(10) & Me.tb_Diag2

ElseIf Me.tb_Diag1 <> "" And _
Me.tb_Diag2 = "" And _
Me.tb_Diag3 = "" And _
Me.tb_Diag4 = "" Then
DiagList = Me.tb_Diag1
Else
DiagList = ""
End If



If DiagList <> HCC_WS.Cells(CurRow, 7) Then
If HCC_WS.Cells(CurRow, 7) <> "" Then
Ans = MsgBox("A different Diagnosis already exists, would you like to overwrite the current Diagnosis?", vbYesNo)
Select Case Ans
Case vbYes
HCC_WS.Cells(CurRow, 7) = DiagList
Case vbNo
End Select
Else
HCC_WS.Cells(CurRow, 7) = DiagList
End If
End If

 

[Fluff]

How about replacing the whole thing with

diaglist = Replace(Application.Trim(Me.tb_Diag1 & " " & Me.tb_Diag2 & " " & Me.tb_Diag3 & " " & Me.tb_Diag4), " ", Chr(10))

 

[Drumwaves]

Useful, but doesn't fit my needs.

Fyi - each Me.tb_diagx will contain a lengthy description more than a single word. The Trim is splitting apart tb_Diag1 and Chr(10) at every " ".

For instance if....
tb_diag1 = "7238 How are you doing today" and
tb_diag2 = "1234 Just fine thank you"
tb_diag3 = "321 i need some help"
tb_diag4 = "5678 we're getting close"

DiagList should equal the following in one cell...

7238 How are you doing today
1234 Just fine thank you
321 i need some help
5678 we're getting close


(4 sets of strings with 3 Alt+Enters or 3 Chr(10) to put 4 strings on 4 lines)

Example 2:


For instance if....
tb_diag1 = "7238 How are you doing today" and
tb_diag2 = "1234 Just fine thank you"
tb_diag3 = ""
tb_diag4 = ""

DiagList should equal the following in one cell...

7238 How are you doing today
1234 Just fine thank you

(2 sets of strings with 1 Alt+Enters or 1 Chr(10) to put 2 strings on 2 lines)

Hope that helps to clear up my objective.

 

[Fluff]

Ok, how about

diaglist = Replace(Replace(Application.Trim(Replace(Replace(Me.tb_Diag1 & "|" & Me.tb_Diag2 & "|" & Me.tb_Diag3 & "|" & Me.tb_Diag4, " ", "#"), "|", " ")), " ", Chr(10)), "#", " ")

This assumes that your strings wont include either | or #

 

[Drumwaves]

That seems to do the trick. I wouldn't have thought of that.

Thank you!!

 

[Fluff]

You're welcome & thanks for the feedback