Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Insert "X" every certain number of columns after finding "X", based on value of cell in same row

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Insert "X" every certain number of columns after finding "X", based on value of cell in same row

    Hello all,

    I am trying to write VBA to do a specific task, and I am almost there. I simply need a little bit of help to figure out how to do the remaining actions.

    Here is what I am trying to do with this code:
    1) For rows 11 to 185, search range L11:DK185 for the cell value "X"
    2) If "X" is found, insert another "X" in this same row ONLY (to the right), "Y" number of columns based on the value of that row's "J" column (Which is next to Day, in column "K")
    3) Loop this and insert "X" every "Y" number of columns in this row until you reach column DK, where I no longer want X's afterwards
    4) Repeat this for every row, individually.

    For example, if I have the value "4" in column J of row 17, and an "X" in cell "AS17": I want to insert "X" every 4 columns to the right of this row, i.e place an "X" in cells AW17, AA17, etc.. until I reach DK17.

    This is my code so far, but it is not functioning properly. It places the "X" all in row 11, rather than in the same row the searched "X" is found in, and it does not repeat the process until column "DK".

    Thanks in advance for your help! Been breaking my head over this one for a while.

    PS: I'm thinking the code should just be remade from scratch, I'm a super beginner at this and this code is probably not even the right way to look at the situation.


    Code:
    Sub InsertX()
    
    
        Application.ScreenUpdating = False
        Dim day As Range, fnd As Range, srcWS As Worksheet
        Set srcWS = Sheets("PLAN 2019")
        
        For Each day In srcWS.Range("K11:K185")
            Set fnd = srcWS.Range("L11:DK185").Find("X", LookIn:=xlValues, LookAt:=xlWhole)
            If Not fnd Is Nothing Then
                fnd.Offset(0, (day.Offset(0, -1).Value)).Value = "X" 'WOULD LIKE TO CHANGE THIS TO TAKE VALUE FROM COLUMN "J" OF ACTIVECELL'S ROW RATHER THAN DO SO BY OFFSET
            End If
        Next day
    'INSERT LOOP HERE TO REDO UNTIL COLUMN DK
        Application.ScreenUpdating = True
        
    End Sub

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,211
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Insert "X" every certain number of columns after finding "X", based on value of cell in same row

    Note that if there is more than one "X" in any row, you will not necessarily get the uniform spacing between "X's" of the value in col J after you run this macro. I have not included any error handling in case col J contains a negative value, is empty or is non-numeric.
    Code:
    Sub chirolove()
    Dim R As Range, Vin As Variant, Y As Variant, i As Long, j As Long, k As Long
    Const F As String = "X"
    Set R = Range("L11:DK185")
    Vin = R.Value
    Y = Range("J11:J185").Value
    For i = 1 To UBound(Vin, 1)
        For j = 1 To UBound(Vin, 2)
            If Vin(i, j) = F Then
                For k = j + Y(i, 1) To UBound(Vin, 2) Step Y(i, 1)
                    Vin(i, k) = F
                Next k
            End If
        Next j
    Next i
    R.Value = Vin
    End Sub
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert "X" every certain number of columns after finding "X", based on value of cell in same row

    Hey JoeMo,

    I get run-time error 13 (Type Mismatch) when trying to run the code, at line:

    Code:
            If Vin(i, j) = F Then
    As to answer your inquiries, there is never originally more than one "X" per row, and col J always contains a positive integer and nothing else. So this is fine as it is if it ends up working!

    Thank you for your help.

    Quote Originally Posted by JoeMo View Post
    Note that if there is more than one "X" in any row, you will not necessarily get the uniform spacing between "X's" of the value in col J after you run this macro. I have not included any error handling in case col J contains a negative value, is empty or is non-numeric.
    Code:
    Sub chirolove()
    Dim R As Range, Vin As Variant, Y As Variant, i As Long, j As Long, k As Long
    Const F As String = "X"
    Set R = Range("L11:DK185")
    Vin = R.Value
    Y = Range("J11:J185").Value
    For i = 1 To UBound(Vin, 1)
        For j = 1 To UBound(Vin, 2)
            If Vin(i, j) = F Then
                For k = j + Y(i, 1) To UBound(Vin, 2) Step Y(i, 1)
                    Vin(i, k) = F
                Next k
            End If
        Next j
    Next i
    R.Value = Vin
    End Sub

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,211
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Insert "X" every certain number of columns after finding "X", based on value of cell in same row

    Works fine for me when F="X" (a string). What's your constant F set equal to? If not a string, change the const line to:

    Const F as Variant = whatever you have
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert "X" every certain number of columns after finding "X", based on value of cell in same row

    Thanks for the quick reply,

    I legitimately just copy and pasted your whole code without changing anything, I don't know why it would work for you and not me I'm using Excel 2013, although I highly doubt that has anything to do with it...

    Thank you again,

    Quote Originally Posted by JoeMo View Post
    Works fine for me when F="X" (a string). What's your constant F set equal to? If not a string, change the const line to:

    Const F as Variant = whatever you have

  6. #6
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,211
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Insert "X" every certain number of columns after finding "X", based on value of cell in same row

    Quote Originally Posted by Chirolove View Post
    Thanks for the quick reply,

    I legitimately just copy and pasted your whole code without changing anything, I don't know why it would work for you and not me I'm using Excel 2013, although I highly doubt that has anything to do with it...

    Thank you again,
    Well, in that case the string "X" is being used as the search term. Do you have "X" (w/o the quote marks) as the entire cell content of at least one cell in the range L11:DK185 ...... and please note that the code as written is case sensitive (easily changed to insensitive if desired) so that's an uppercase X you must have. If you do, then regardless of your version of Excel, it should work for you unless there's something else happening you have failed to mention.

    Or is it the case that your cells contain "X" with the quote marks around the X?
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  7. #7
    New Member
    Join Date
    Jul 2019
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert "X" every certain number of columns after finding "X", based on value of cell in same row

    Hey JoeMo,

    Yes, entire cell content is indeed a capital X without the quotation marks.

    Now that you mention it, I figured this was definitely something on my end and that I must have forgotten to tell you something, so I tried the code in a new, blank workbook. Turns out no type mismatch error here, so I wondered what could be the problem and realized the document I need the code to work in is in the form of a table! That most likely has something to do with the problem, since the formatting is different than for a normal cell.

    Is there a way to modify the code to make it apply to a table?

    I'm sorry for forgetting to mention this rather important information, I actually forgot it was a table. The code works wonders in normal cells!

    Thank you very much and have a nice day,

    Quote Originally Posted by JoeMo View Post
    Well, in that case the string "X" is being used as the search term. Do you have "X" (w/o the quote marks) as the entire cell content of at least one cell in the range L11:DK185 ...... and please note that the code as written is case sensitive (easily changed to insensitive if desired) so that's an uppercase X you must have. If you do, then regardless of your version of Excel, it should work for you unless there's something else happening you have failed to mention.

    Or is it the case that your cells contain "X" with the quote marks around the X?

  8. #8
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,211
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Insert "X" every certain number of columns after finding "X", based on value of cell in same row

    Quote Originally Posted by Chirolove View Post
    Hey JoeMo,

    Yes, entire cell content is indeed a capital X without the quotation marks.

    Now that you mention it, I figured this was definitely something on my end and that I must have forgotten to tell you something, so I tried the code in a new, blank workbook. Turns out no type mismatch error here, so I wondered what could be the problem and realized the document I need the code to work in is in the form of a table! That most likely has something to do with the problem, since the formatting is different than for a normal cell.

    Is there a way to modify the code to make it apply to a table?

    I'm sorry for forgetting to mention this rather important information, I actually forgot it was a table. The code works wonders in normal cells!

    Thank you very much and have a nice day,
    If the table databody range is L11:DK185 the code won't care if the range is part of a table. There's nothing "abnormal" about cells in a table that would cause a type mismatch error. There must be something else causing this.

    Try turning the range you used in the "new blank workbook" into a table with L11:DK185 as the databody range and see if the code works there.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  9. #9
    New Member
    Join Date
    Jul 2019
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert "X" every certain number of columns after finding "X", based on value of cell in same row

    I tested in another workbook table and you're right, the table format in no way affects the macro. I'm sorry, I should have tried that first, before posting.

    Anything else that could possibly be causing this? I'm stunned...

    Quote Originally Posted by JoeMo View Post
    If the table databody range is L11:DK185 the code won't care if the range is part of a table. There's nothing "abnormal" about cells in a table that would cause a type mismatch error. There must be something else causing this.

    Try turning the range you used in the "new blank workbook" into a table with L11:DK185 as the databody range and see if the code works there.

  10. #10
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,211
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Insert "X" every certain number of columns after finding "X", based on value of cell in same row

    Quote Originally Posted by Chirolove View Post
    I tested in another workbook table and you're right, the table format in no way affects the macro. I'm sorry, I should have tried that first, before posting.

    Anything else that could possibly be causing this? I'm stunned...
    Hard to even guess at what might be causing the problem without being able to see the workbook in which it occurs. Could be its corrupted in some way. Thanks for the feedback that exonerates the code I posted.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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