N Function Comments Not Working

[Matteous]

Hi everyone,
I'm trying to insert comments from a separate tab into a timesheet summary table. After lots of VBA searching I found that the "N" function would do the trick.

Essentially, my formula would be =(Employees Hours)+N("Employees comment")
but I'm getting absolutely no joy.

I've tried downloading an example of this formula from the myonlinetraininghub website, and it works fine. BUT... if I copy the formula manually then paste it, no comment. If I paste special > paste formula, no comment. If I do a straight up ctrl+c, ctrl+v, the comment appears.

Can anyone tell me why the N Function doesn't want to work (or at least work consistently??). This would be a MASSIVE timesaver for me, but it just doesn't want to work!

Thanks all,
Matt

[Aflatoon]

The N function does not add a comment to the cell - it simply appears in the formula to let you know what is happening without affecting the numeric output.

[Matteous]

Hi Aflatoon,
Thank you for your reply, but I beg to differ. I can't post a link to the sites I've visited but a number of sites have said it's possible.
Please search for "comments excel n function" in Google and have a look.

Please note, I am extremely thankful for your help and in no way am I doubting you, I'm just saying what I've seen.

Thanks again,
Matt

[Aflatoon]

I think you may have misunderstood what you read. The only 'comment' that is added to the cell is whatever text you put inside the N formula. It does not add a true comment (the little red triangle in the top right corner and the popup text when you mouseover the cell).
The N function is used to convert numeric text into a true number, nothing more. The reason it is used to 'comment' numeric formulas is that N("any non-numeric text") returns 0 and so does not affect the final result but allows you to put a description into the formula explaining what it is doing. The reason you saw the comment when you copied and pasted the demo cell but not when you simply copied the formula is that someone had manually added a true Excel comment to that cell.

[Bob Phillips]

Matt,

I have shown this trick numerous times, and I also did a Google as you suggest, and I just cannot see how you come to the conclusion that you have. I even went to myonlinetrainghub.com and the explanation in column D is text, it does not come from any formula.

Post the comments that you think say otherwise.

[Paul_Hossler]

http://www.myonlinetraininghub.com/microsoft-excel%E2%80%99s-n-function

Possilbly the word 'comment' is ambigious.

Excel has a [Insert Comment] capability (right click in a cell), but the N() examples seem to be more 'reminders' within a cell formula that don't affect the result

Even the training site says that it's "unobtrusive" and not the "annoying Comments ..."





As you can see the N function component of the formula has no impact on the result, but it allows you to insert an unobtrusive note in your spreadsheet that explains your rationale without the need for what can be annoying Comments like this.



Paul

[Teeroy]

Matt,

The articles you have mentioned are about commenting the formula to make it understandable. The comment that you seem to expect is a comment object that is attached to a cell. You can add the comment object to each cell using VBA to get what you want. There are a number of good examples at http://www.contextures.com/xlcomments03.html that may help you.

[Matteous]

Thank you all for your kind help! I DID think it was a bit strange that the Excel Help file makes no mention of the N function being able to put in comments.

I'll investigate the links provided and see what I can do.

Thanks again!
Matt

[Matteous]

I've had a look through the link Teeroy and I can't find what I need.

Basically I need to take the comments from a csv export, and find a way of including them as comments against the relevant entry in the manhour summary (image attached).

I'm guessing this will involve using a combination of VLOOKUP & HLOOKUP to find the corresponding employee, date and project... but I'm a complete novice when it comes to building my own code.

I would really appreciate your help!

Regards,
Matt

[Teeroy]

Can you attach a sample workbook showing the format of the comment data and explaining how you need to match the comment against the cell? The data just has to be representative, don't include any sensitive / confidential information.

[Matteous]

Hi Teeroy,
I've attached a culled sample.

You'll find two tabs - Manhour Summary (Hrs) and Time Log Review - Comments.

The Manhour Summary (Hrs) tab is intended for all managers to use, while the other tab will remain hidden.

I need the comment that appears at the end of each line on the Time Log Review tab to appear against the relevant entry on the Manhour Summary (ie. "Annual Leave Day 1" appears as a comment on cell K612 of the Manhour Summary, "Annual Leave Day 2" appears as a comment on cell L612 of the Manhour Summary etc etc.)

Thank you for your help : )
Matt

[Teeroy]

Matt,

How does the raw data get into the table? Is it by VBA, formula or manual cut and paste? I'm just trying to work out whether there is a point in the process where the data remains collated rather than try to reconnect via "best guess" relating the data (as there is no primary key).

[Matteous]

Straight up copy paste job from a CSV file.
The columns on the left hand side breaking down the date, project number etc are my addition, everything else is untouched from the import/export.

[Matteous]

Any joy? I'm hoping to get it up and running during my holidays (sad I know)...

Thanks everyone : )

[p45cal]

try adding these two to a standard code module and running blah:Sub blah()
Set ms = Sheets("Manhour Summary (Hrs)")
x = Intersect(ms.UsedRange, ms.Rows(6))
Set colmB = Intersect(ms.UsedRange, ms.Columns(2))
With Sheets("Time Log Review - Comments")
For Each cll In .Range(.Range("J3"), .Cells(.Rows.Count, "J").End(xlUp)).Cells
y = cll.Offset(, 1).Value
y = CLng(y)
colm = 0
For i = 9 To 39
If CLng(x(1, i)) = y Then
colm = i
Exit For
End If
Next i
If colm > 0 Then
rw = 0
Set rngrw = colmB.Find(cll.Value)
If Not rngrw Is Nothing Then
rw = rngrw.Row
With ms.Cells(rw, colm)
If HasComment(ms.Cells(rw, colm)) Then
'extend the comment:
.Comment.Text Text:=.Comment.Text & vbLf & cll.Offset(, 13).Value
Else
'add the comment:
.AddComment cll.Offset(, 13).Value
End If
End With
End If
End If
Next cll
End With
End Sub
Function HasComment(theCell) As Boolean
Dim y As Comment
On Error Resume Next
Set y = theCell.Comment
If Not y Is Nothing Then HasComment = True
End Function
It'll probably fall over if there isn't a date in column K of the Time log review, and the code is more convoluted to cope with .Find not working well with dates. If there's already a cell comment in a cell it will add to it, so if you run the blah twice you'll get a doubling up of the comment in each cell.