Forum Discussion

BuddyGlenn's avatar
BuddyGlenn
Copper Contributor
Aug 27, 2020

insert value every nth row in a cell on that row

I know how to highlight every nth row in a sheet, I also need to place a name in a cell on the highlighted row.  For instance, I would like to place "Sally" in column R for every 100th row.  Please h...
excel
ExcelExciting's avatar
ExcelExciting
MVP
Aug 28, 2020

Hi BuddyGlenn,

 

You can use the mod function and wrap it in If function, below example might be helpful where every 5th row I'm adding the name Sally

 

 

=IF(MOD($A2,5)=0,"Sally","")

 

 

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more

 

 

  • BuddyGlenn's avatar
    BuddyGlenn
    Copper Contributor
    Aug 28, 2020

    ExcelExciting Thank you for your help Faraz!  I am trying to add "Sally" to column S (19) for every 200th row in my spreadsheet.   I am able to highlight every row using conditional formatting with this formula:

     

    =MOD(ROW(),200)=0 

     

    and the above works highlighting the rows

     

    I was trying to incorporate adding "Sally" in column 19 for each row hightlighted.

     

    I know 

    =IF(MOD($A2,19)=0,"Sally","") will put in correct column, I need it to be every 200th column.  I cannot seem to get the correct syntax for this.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Aug 29, 2020

      BuddyGlenn 

      Perhaps

      =IF(NOT(MOD(ROW(),19))*NOT(MOD(COLUMN(),200)), "Sally", "")
      • BuddyGlenn's avatar
        BuddyGlenn
        Copper Contributor
        Aug 31, 2020

        SergeiBaklan Thank you very much Sergei! I had to have completed by Friday afternoon so I did a quick macro that worked.  I will get your solution a try as well!

         

        Sub TagHighlightRows()

        Dim AD As Integer
        Dim SB As Integer

        AD = 100
        SB = 120

        Do Until AD > 16400

         

        Cells(AD, 19).Value = "AD"
        Cells(AD, 19).Interior.Color = vbGreen

        Cells(SB, 19).Value = "SB"
        Cells(SB, 19).Interior.Color = vbMagenta


        AD = AD + 700
        SB = SB + 700

        Loop

         

        End Sub

    • ExcelExciting's avatar
      ExcelExciting
      MVP
      Aug 29, 2020

      Hi BuddyGlenn 

       

      can you please attached the sample data for you error and what you need enter manually in the file 

       

      Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

      If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more

      • BuddyGlenn's avatar
        BuddyGlenn
        Copper Contributor
        Aug 31, 2020

        ExcelExciting 

        Thank you very much Faraz! I had to have completed by Friday afternoon so I did a quick macro that worked.  

         

        Sub TagHighlightRows()

        Dim AD As Integer
        Dim SB As Integer

        AD = 100
        SB = 120

        Do Until AD > 16400

         

        Cells(AD, 19).Value = "AD"
        Cells(AD, 19).Interior.Color = vbGreen

        Cells(SB, 19).Value = "SB"
        Cells(SB, 19).Interior.Color = vbMagenta


        AD = AD + 700
        SB = SB + 700

        Loop

         

        End Sub

Resources