none
VBA code to enter constant values in every rows if col C contains data RRS feed

  • Question

  • Hello Developers!

    I have been looking for a code that will enter constant values for every 3 rows in Col A
    when  a certain column has a data. For example, for every data that is in Col C, Col A1, A2, A3 will contain ABC, DEF, GHI

    Below is what's supposed to look like: 

    Thanks in advance for recommendations!

    'If C is not empty enter ABC, DEF, GHI on A1, A2, and A3 respectively
    
           For Each r In Range("C1", Range("C" & Rows.Count).End(xlUp))
        If r = "" Then
          Range("A") = Range("A") & "ABC", "DEF", "GHI"
        End If
      Next

    Wednesday, April 18, 2018 7:01 PM

Answers

  • For example:

    Sub Test()
        Dim r As Range
        Application.ScreenUpdating = False
        For Each r In Range("C1", Range("C" & Rows.Count).End(xlUp))
            If r <> "" Then
                r.Offset(0, -2) = "ABC"
                r.Offset(1, -2) = "DEF"
                r.Offset(2, -2) = "GHI"
            End If
        Next r
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by IamJackie Thursday, April 19, 2018 3:18 PM
    Wednesday, April 18, 2018 7:22 PM

All replies

  • For example:

    Sub Test()
        Dim r As Range
        Application.ScreenUpdating = False
        For Each r In Range("C1", Range("C" & Rows.Count).End(xlUp))
            If r <> "" Then
                r.Offset(0, -2) = "ABC"
                r.Offset(1, -2) = "DEF"
                r.Offset(2, -2) = "GHI"
            End If
        Next r
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by IamJackie Thursday, April 19, 2018 3:18 PM
    Wednesday, April 18, 2018 7:22 PM
  • Thank you so much Hans!!! Worked to a T!!!

    Thursday, April 19, 2018 3:19 PM