Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Apr 2015
    Posts
    23
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Copy value if cell is empty

    Hi,

    With this formula the values are in Column L.
    Code:
    Sub CopyFormulas()
    With ActiveSheet
        .Range("J2").Formula = "=IF(I2=10," & """1010""" & ",IF(I2=11," & """1111""" & "," & """1414""" & "))"
        .Range("K2").Formula = "=RIGHT(CONCATENATE(" & """00000000""" & ",D2),8)"
        .Range("L2").Formula = "=CONCATENATE(B2,J2,K2)"
        .Range("J2:L2").AutoFill Destination:=Range("J2:L" & Range("B" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault
    End With
    End Sub
    Now if any cell in Column H is EMPTY or Flagged as 'Not Found' fill the value from Column L cell and to be highlighted in red color when filled in Col H cell.

    Thanks.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Danny,

    Try this code. Revisions are in blue

    Code:
    Sub CopyFormulas()
    Dim cell As Range, rng As Range
    With ActiveSheet
        Lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
        Set rng = .Range("H2:H" & Lastrow)
        .Range("J2").Formula = "=IF(I2=10," & """1010""" & ",IF(I2=11," & """1111""" & "," & """1414""" & "))"
        .Range("K2").Formula = "=RIGHT(CONCATENATE(" & """00000000""" & ",D2),8)"
        .Range("L2").Formula = "=CONCATENATE(B2,J2,K2)"
        .Range("J2:L2").AutoFill Destination:=Range("J2:L" & Lastrow), Type:=xlFillDefault
        For Each cell In rng
            If cell = "" Or cell = "Not Found" Then
                cell = cell.Offset(0, 4)
            End If
        Next cell
    End With
    End Sub
    HTH,
    Maud
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Apr 2015
    Posts
    23
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi,

    Excellent Maud.

    If we can highlight those "Not Found" and Empty cells then we can easily identify it.

    Thank you.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Danny,

    Add the additional line in blue
    Code:
    Sub CopyFormulas()
    Dim cell As Range, rng As Range
    With ActiveSheet
        Lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
        Set rng = .Range("H2:H" & Lastrow)
        .Range("J2").Formula = "=IF(I2=10," & """1010""" & ",IF(I2=11," & """1111""" & "," & """1414""" & "))"
        .Range("K2").Formula = "=RIGHT(CONCATENATE(" & """00000000""" & ",D2),8)"
        .Range("L2").Formula = "=CONCATENATE(B2,J2,K2)"
        .Range("J2:L2").AutoFill Destination:=Range("J2:L" & Lastrow), Type:=xlFillDefault
        For Each cell In rng
            cell.Interior.Pattern = xlNone
            If cell = "" Or cell = "Not Found" Then
                cell = cell.Offset(0, 4)
                cell.Interior.Color = vbRed
            End If
        Next cell
    End With
    End Sub
    Last edited by Maudibe; 2016-02-13 at 23:39.

  5. #5
    New Lounger
    Join Date
    Apr 2015
    Posts
    23
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi,

    Thanks Maud, Perfect.

    Thanks.

Posting Permissions

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