Bill_Biggs
Well-known Member
- Joined
- Feb 6, 2007
- Messages
- 1,216
I have created a code just now that does work but I know that there is a better way of writing this code. Here is the mission.
I have a spread sheet that returns values to B5 through J5. Somes time only B5 has data, sometime more of them, up to 8. I need a string created that will say what the values are ie, if B5 is alone then the string will say "Jul09" and if the B5 thru D5 have data, the string will come out as "Jul09,Aug09,Sep09".
This is what I wrote:
Is there a better way to write this? Actually, I am sure therre is, but I don't know it.
I have a spread sheet that returns values to B5 through J5. Somes time only B5 has data, sometime more of them, up to 8. I need a string created that will say what the values are ie, if B5 is alone then the string will say "Jul09" and if the B5 thru D5 have data, the string will come out as "Jul09,Aug09,Sep09".
This is what I wrote:
Code:
Dim A, B, C, D, E, F, G, H, I, K As String
Dim J As Integer
A = Range("B5").Value
B = Range("C5").Value
C = Range("D5").Value
D = Range("E5").Value
E = Range("F5").Value
F = Range("G5").Value
G = Range("H5").Value
H = Range("I5").Value
I = Range("J5").Value
Range("B5").Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Select
Loop
J = ActiveCell.Column - 2
If J < 0 Then J = 1
If J = 1 Then K = "" & A & ""
If J = 2 Then K = "" & A & "," & B & ""
If J = 3 Then K = "" & A & "," & B & "," & C & ""
If J = 4 Then K = "" & A & "," & B & "," & C & "," & D & ""
If J = 5 Then K = "" & A & "," & B & "," & C & "," & D & "," & E & ""
If J = 6 Then K = "" & A & "," & B & "," & C & "," & D & "," & E & "," & F & ""
If J = 7 Then K = "" & A & "," & B & "," & C & "," & D & "," & E & "," & F & "," & G & ""
If J = 8 Then K = "" & A & "," & B & "," & C & "," & D & "," & E & "," & F & "," & G & "," & H & ""
If J = 9 Then K = "" & A & "," & B & "," & C & "," & D & "," & E & "," & F & "," & G & "," & H & "," & I & ""
Is there a better way to write this? Actually, I am sure therre is, but I don't know it.