Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jul 2017
    Posts
    2
    Thanks
    1
    Thanked 1 Time in 1 Post

    How To Compare Value From Another Sheet And Return Value To a Comment

    Hi, Masters
    Can you help me to compare a cell value from DataEntry sheet and MasterData sheet in one workbook using vba? If there is value bigger than 200, it will give me just comment in DataEntry sheet. I attach the sample file

    I've had a look in the Forums and internet using search but nothing close seems to be forthcoming.
    Many thanks for help folks!

    Dedi
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,919
    Thanks
    152
    Thanked 746 Times in 678 Posts
    Try this code:

    In a standard module
    Code:
    Public Sub Check_Values()
    '------------------------
    'DECLARE AND SET VARIABLES
    Dim cell As Range, rng As Range
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim LastRow As Long, row As Long, col As Long
    Set ws1 = Worksheets("DataEntry")
    Set ws2 = Worksheets("MasterData")
    LastRow = ws2.Cells(Rows.Count, 2).End(xlUp).row
    Set rng = ws2.Range("D3:O" & LastRow)
    '------------------------
    'TEST VALUES AND ADD COMMENTS
    For Each cell In rng
        If cell > 200 Then
            row = cell.row:: col = cell.Column
            Comment = "Master Data Sheet:" & vbNewLine & _
                ws2.Cells(2, col) & vbNewLine & _
                ws2.Cells(row, 2) & vbNewLine & _
                "value = " & cell.Value
            ws1.Range(cell.Address).ClearComments
            ws1.Range(cell.Address).AddComment Comment
        End If
    Next cell
    '------------------------
    'CLEANUP
    Set cell = Nothing
    Set rng = Nothing
    End Sub
    HTH,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2017-08-05 at 21:39.

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    MeNewBieToVBA (2017-08-05)

  4. #3
    New Lounger
    Join Date
    Jul 2017
    Posts
    2
    Thanks
    1
    Thanked 1 Time in 1 Post
    Maudibe...IT WORKS LIKE A CHAMP! What you did beyond my expectation. You save my head...Thanks again

  5. The Following User Says Thank You to MeNewBieToVBA For This Useful Post:

    Maudibe (2017-08-08)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •