PDA

View Full Version : Put comment with IF



ced0802
12-04-2015, 05:23 AM
Hello everyone,

I would like to include in my code that it puts comment only if cells in columns "N" of workbooks ("Stocks") = "Y"

Thank you very much for your help.




Sub position()

Dim ws As Worksheet, rng As Range, x As Long, temp

Set ws = Workbooks("Wk49production").Sheets("packing production schedule")
Set rng = Workbooks("Stocks").Worksheets("wms_browse").Range("D2:Q2000")

For x = 1 To 1000
With ws.Cells(x, 5)
temp = Application.VLookup(ws.Cells(x, 35), rng, 14, False)

If .Comment Is Nothing Then
.AddComment CStr(temp)

Else
.Comment.Text CStr(temp)


End If
End With
Next
End Sub

mancubus
12-04-2015, 07:57 AM
i think you mean 'corresponding cell' in col N

perhaps:


Sub vbax_54455_AddComment_On_Condition()

Dim ws As Worksheet, rng As Range, x As Long, temp

Set ws = Workbooks("Wk49production").Sheets("packing production schedule")
Set rng = Workbooks("Stocks").Worksheets("wms_browse").Range("D2:Q2000")

For x = 1 To 1000
With ws.Cells(x, 5)
temp = Application.VLookup(ws.Cells(x, 35), rng, 14, False)
If Application.VLookup(ws.Cells(x, 35), rng, 11, False) = "Y" Then
If .Comment Is Nothing Then
.AddComment CStr(temp)
Else
.Comment.Text CStr(temp)
End If
End If
End With
Next

End Sub

ced0802
12-04-2015, 03:14 PM
Hi Mancubus,

Thank you very much for your help. It is close but not working.
I have several times Cells(x, 35) in my workbooks stocks, which can match either with an "N" or "Y".
What I get is the value for the first value it meets (even if it's an N).
Not sure it makes sense :)

mancubus
12-05-2015, 03:21 PM
welcome.

perhaps seeing the workbooks may help.

you can upload a (multiple) workbook(s) via Go Advanced / Manage Attachments.

you may wish to alter your sensitive / confidential data. just provide some fake data in order to understand the table structure and values.