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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,866
Messages
5,834,073
Members
430,260
Latest member
MANICX100

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