Comment

[vss]

H,

I have a sheet1 with tasks in first column and Dates in first row

In sheet2 I have Tasks, Dates, and desc


I need a macro to display the description as a comment in sheet1 with respect to task and date in the cell below the relevant date column and task row.

please let me know how to do this

 

[bobhc]

Good morning vss

A bit early and I have not drunk enough coffee and I am probably not understanding you needs correctly but why can you not just insert a comment.Unless you mean you need VBA to auto update a sheet that all ready has data, if you do then you will need one of the VBA gurus to answer

 

[Hui]

Vss


Firstly, Welcome to the Chandoo.org forums.


It will be something like:

=INDEX(Sheet2!$C$2:$C$100,MATCH($A2&B$1,Sheet2!$A$2:$A$100&Sheet2!$B$2:$B$100,0))

Ctrl+Shift+Enter


adjust 100 to be the maximum number of rows of your data

DONOT use $C:$C etc as it won't work

 

[SirJB7]

Hi, vss!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Comment%20%28for%20vss%20at%20chandoo.org%29.xlsm


Here's the code:

-----

[pre]

Option Explicit

Sub SetComments()
' constants
Const ksWSData = "Hoja1"
Const ksRngData = "DataTable"
Const ksWSDescription = "Hoja2"
Const ksRngDescription = "DescriptionTable"
' declarations
Dim rngData As Range, rngDescription As Range
Dim I As Long, J As Integer, K As Long
Dim A As String, B As String, D As Date
' start
Set rngData = Worksheets(ksWSData).Range(ksRngData)
Set rngDescription = Worksheets(ksWSDescription).Range(ksRngDescription)
' process
With rngData
For I = 1 To .Rows.Count
' task
A = .Cells(I, 1).Offset(0, -1).Value
For J = 1 To .Columns.Count
' date
D = .Cells(1, J).Offset(-1, 0).Value
' description
B = ""
For K = 1 To rngDescription.Rows.Count
If rngDescription.Cells(K, 1).Value = A And _
rngDescription.Cells(K, 2).Value = D Then Exit For
Next K
If K <= rngDescription.Rows.Count Then B = rngDescription.Cells(K, 3).Value
' comment
If B <> "" Then
With .Cells(I, J)
If Not (.Comment Is Nothing) Then .Delete
.AddComment
.Comment.Text B
End With
End If
Next J
Next I
End With
' end
Set rngDescription = Nothing
Set rngData = Nothing
Beep
End Sub

[/pre]
-----


Regards!

 

[vss]

Both solutions are perfect Sir, you both have saved my day

 

[SirJB7]

Hi, vss!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!