# adding text according to col with equal date

J

#### Jack Sons

Hi all,

In the active sheet in a certain cell I want to add text to the existing
content as follows.
If the date in row 62+TT and column K
equals the date in row 62+TT and column D I want to add "abc",
equals the date in row 62+TT and column E I want to add "def"
equals the date in row 62+TT and column F I want to add "ghi"
equals the date in row 62+TT and column G I want to add "jkl"
equals the date in row 62+TT and column H I want to add "mno"
equals the date in row 62+TT and column I I want to add "pqr"
equals the date in row 62+TT and column J I want to add "stu".
In any and only one of these columns that date is present.

In fact I already have the code below the dotted line, where the part
(stupid part, I know! it is just to show wat I mean) that off course does
not work is
'& " " & Chr(10) & _
' if Range(Cells(62 + TT, "K"), Cells(62 + TT, "K")) = Range(Cells(62 + TT,
"J"), Cells(62 + TT, "J")) Then "stu"

As you see what I want is to add a fourth line of text in the active cell
that contains abc, or def, or ghi etc. according to in which column we find
the date equal to the date in row 62+TT and column K.
Please show me the most simple - or (preferably) the most elegant/efficient,
if it is not too difficult to grasp - way to accomplish this without
creating helper columns.

Jack Sons
The Netherlands

--------------------------------------------------------------------------------------------------------------------------
With ActiveSheet.Range(Cells(62 + TT, 1), Cells(62 + TT, 1))
.Value = Range(Cells(2 + TT, "A"), Cells(2 + TT, "A")).Value & " " &
Chr(10) & _
Range(Cells(2 + TT, "B"), Cells(2 + TT, "B")).Value & " " &
Chr(10) & _
Format(Range(Cells(2 + TT, "K"), Cells(2 + TT, "K")).Value,
"dd mmmm yyyy") '& " " & Chr(10) & _
' if Range(Cells(62 + TT, "K"), Cells(62 + TT, "K"))=
Range(Cells(62 + TT, "J"), Cells(62 + TT, "J")) Then "stu"

With .Characters(Start:=1, Length:=Len(Range(Cells(2 + TT, "A"),
Cells(2 + TT, "A")).Value)).Font
.Bold = True
.ColorIndex = 3
.Underline = True
.Size = 10
.Name = "Times New Roman"
End With

With .Characters(Start:=Len(Range(Cells(2 + TT, "A"), Cells(2 + TT,
"A")).Value) + 2, _
Length:=Len((Range(Cells(2 + TT, "B"), Cells(2 + TT,
"B")).Value)) + (Range("K2").Value)).Font
'Length:= 100).Font
.ColorIndex = 1
.Underline = False
.Bold = False
.Name = "Arial"
.Size = 7
End With

L

#### Luke M

'I would use the Choose Case format. You can define all the possible choices
and their respective outcomes, and then let the macro choose the correct
result. Hopefully this example provides insight.

Sub ChooseCase()
'Assumes TT is defined somehow...
TT = 6

MyDate = Cells(62 + TT, "K")

Select Case MyDate
Case Cells(62 + TT, "D")
Case Cells(62 + TT, "E")
Case Cells(62 + TT, "F")
Case Cells(62 + TT, "G")
Case Cells(62 + TT, "H")
Case Cells(62 + TT, "I")
Case Cells(62 + TT, "J")
Case Else
End Select

'Change activecell to new value
ActiveCell = ActiveCell.Value & Chr(10) & xAdd
End Sub

J

#### Jack Sons

Luke,

You were fast as lightning!
TT is indeed defined, it's a counter in a do loop.
Your code works like charm. Never used case before, learned a lot.
Thanks.

Jack.