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.
Your assistance will be appreciated very much. Thanks in advance.

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
 
Ad

Advertisements

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
'Determine which extension to add
Case Cells(62 + TT, "D")
xAdd = "abc"
Case Cells(62 + TT, "E")
xAdd = "def"
Case Cells(62 + TT, "F")
xAdd = "ghi"
Case Cells(62 + TT, "G")
xAdd = "jkl"
Case Cells(62 + TT, "H")
xAdd = "mno"
Case Cells(62 + TT, "I")
xAdd = "pqr"
Case Cells(62 + TT, "J")
xAdd = "stu"
Case Else
xAdd = ""
End Select

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

Advertisements

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top