+ Reply to Thread
Results 1 to 3 of 3

Integrate two pieces of code

Hybrid View

  1. #1
    Forum Contributor Rick_Stanich's Avatar
    Join Date
    11-21-2005
    Location
    Ladson SC
    MS-Off Ver
    Office365
    Posts
    1,177

    Integrate two pieces of code

    I cant seem to get this one. I am trying to interate two sections of code into one.
    #1: Collects data and creates a range
    Sub MinMax()
    
        With Sheets("Sheet3")    'Loop thru Column A
            Sh3LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
            Set Sh3Range = .Range("A6:A" & Sh3LastRow)
        End With
        'Format data
        For Each Sh3Cell In Sh3Range
            If Sh3Cell.Offset(0, 13).Value <> "" Then
                Sh3Cell.Offset(0, 4).Value = Sh3Cell.Offset(0, 12).Value & "-" & Sh3Cell.Offset(0, 13).Value
                Sh3Cell.Offset(0, 12).Clear
                Sh3Cell.Offset(0, 13).Clear
            Else
                Sh3Cell.Offset(0, 4).Value = Sh3Cell.Offset(0, 12).Value
                Sh3Cell.Offset(0, 12).Clear
                Sh3Cell.Offset(0, 13).Clear
            End If
        Next Sh3Cell
    
    End Sub
    #2: Find "comment" in a cell for column "E" only.
    Sub FindComment()
      
        With Sheets("Sheet3")    'Loop thru Column A
            Sh3LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
            Set Sh3Range = .Range("A6:A" & Sh3LastRow)
        End With
        
        For Each Sh3Cell In Sh3Range
        Sh3Cell.Offset(0, 4).Activate
        With ActiveCell
            If .Comment Is Nothing Then
                'Do code here
                MsgBox "No Comment"
            Else
                'Do code here
                MsgBox "Yes Comment"
            End If
        End With
            Next Sh3Cell
    End Sub
    The goal is if "E6" through infinity has a comment I dont want to overwrite the contents of that cell.
    Both examples of code run fine, its integrating them thats has me stumped.

    As allways, help, tips and or hints are welcome.
    Regards

    Rick
    Win10, Office 365

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,482

    Re: Integrate two pieces of code

    Like this?

        With Sheets("Sheet3")    'Loop thru Column A
            Sh3LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
            Set Sh3Range = .Range("A6:A" & Sh3LastRow)
        End With
        'Format data
        For Each Sh3Cell In Sh3Range
            If Sh3Cell.Offset(0, 4).Comment Is Nothing Then
                If Sh3Cell.Offset(0, 13).Value <> "" Then
                    Sh3Cell.Offset(0, 4).Value = Sh3Cell.Offset(0, 12).Value & "-" & Sh3Cell.Offset(0, 13).Value
                    Sh3Cell.Offset(0, 12).Clear
                    Sh3Cell.Offset(0, 13).Clear
                Else
                    Sh3Cell.Offset(0, 4).Value = Sh3Cell.Offset(0, 12).Value
                    Sh3Cell.Offset(0, 12).Clear
                    Sh3Cell.Offset(0, 13).Clear
                End If
            End If
        Next Sh3Cell
    Cheers
    Andy
    www.andypope.info

  3. #3
    Forum Contributor Rick_Stanich's Avatar
    Join Date
    11-21-2005
    Location
    Ladson SC
    MS-Off Ver
    Office365
    Posts
    1,177

    Re: Integrate two pieces of code

    I can't believe I stared at this for hours and could not get it. That was probaly way to easy for you.

    Thank you, works perfectly!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1