Hard one, add to comment if matching

Shadow123

Board Regular
Joined
Aug 20, 2012
Messages
124
Hi,
This a little complicated to explain but ill try, let me know if anything isn't clear...

Book 1, sheet 1, coloum O. Take that value, and match it to a list in Book2, sheet1, col X.

Every time there is a match, ie O matches X, take the value book1, sheet1 in C, and paste it into a comment bubble in book2, sheet1, col K.

For example: cell (book1)07 matches (book2)X28, copy the value in C7 (book1), into a comment in K28. then check next cell in column O and so on ...


(there will be more than one match, so whatever is already in a comment bubble needs to stay there, not be overwrtiten)



I think that is the clearest i can make it, I know its a big ask this one... i don't even know how to start this one im afraid.


Any help appreicated :) as always.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Shadow123

Board Regular
Joined
Aug 20, 2012
Messages
124
Anyone?

I have this code that added comments to each cell if that helps? i have tried to edit it, but im totally lost

Code:
Sub testing()
Dim Sh1 As Worksheet
    Dim Sh2 As Worksheet
    Dim Rng1 As Range
    Dim Rng2 As Range
    Dim cell As Range
    Dim Pos As Integer
    Dim MktCP As String
    Dim MktDate As Date
    Dim MktDigits As Long
    Dim r As Long
    Dim Cmt As Comment
    'adjust to correct sheet
    Set Sh1 = Worksheets("Sheet1")
    Set Sh2 = Worksheets("Current Day")
    With Sh1
        Set Rng1 = .Range("o3:o" & .Range("o" & .Rows.Count).End(xlUp).Row)
    End With
    With Sh2
        Set Rng2 = .Range("x4:x" & .Range("x" & .Rows.Count).End(xlUp).Row)
    End With
    For Each cell In Rng1
        Set Cmt = Nothing
        With cell
            If .Value > "" Then
                With Rng2
                    For r = 1 To .Rows.Count
                        If cell.Value = "match" Then
                            If Val(.Cells(r, 10).Value) * 1000 = MktDigits Then
                                If MktCP = "C" Then
                                    With .Cells(r, 4)
                                        .Value = cell.Offset(, 2).Value
                                        Set Cmt = .Comment
                                        If Cmt Is Nothing Then
                                            .AddComment Text:=cell.Offset(, 6).Value
                                        Else
                                            Cmt.Text Text:=cell.Offset(, 6).Value
                                        End If
                                    End With
                                
                                Exit For
                            End If
                        End If
                    Next r
                End With
            End If
        End With
    Next cell
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,608
Messages
5,597,143
Members
414,129
Latest member
mr vba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top