Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

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

  1. #11
    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

    Nonono this code is impeccable, the problem is definitely from my side! You were top notch with this script.

    I've been playing around with it a bunch and it serves the purpose I wanted 100%, as I'm playing with values in my chart I'll end up figuring out where the problem occurs.

    Thank you very much for your help!

    Quote Originally Posted by JoeMo View Post
    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.

  2. #12
    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
    Nonono this code is impeccable, the problem is definitely from my side! You were top notch with this script.

    I've been playing around with it a bunch and it serves the purpose I wanted 100%, as I'm playing with values in my chart I'll end up figuring out where the problem occurs.

    Thank you very much for your help!
    You are welcome - thanks for the reply.
    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. #13
    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

    Oh my god, I finally found the problem! Turns out the person who gave me this file had put conditional formatting to hide error cells (#NUM !). As I corrected some of these and ajusted the code to act on only certain rows, I realized that the code worked just fine when that row contained no error cells!

    Is there a way to modify the code to make it ignore #NUM ! cells? I've got wayyy to many cells to deal with to correct these one at a time, especially since I have a few of these documents to run the macro on and they all have the same problem.

    What a headache figuring this out... finally found the problem!

    Thanks again!

    Quote Originally Posted by JoeMo View Post
    You are welcome - thanks for the reply.

  4. #14
    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
    Oh my god, I finally found the problem! Turns out the person who gave me this file had put conditional formatting to hide error cells (#NUM !). As I corrected some of these and ajusted the code to act on only certain rows, I realized that the code worked just fine when that row contained no error cells!

    Is there a way to modify the code to make it ignore #NUM ! cells? I've got wayyy to many cells to deal with to correct these one at a time, especially since I have a few of these documents to run the macro on and they all have the same problem.

    What a headache figuring this out... finally found the problem!

    Thanks again!
    I assume that means the format is chosen such that if an error is present the cell font is set to match the cell interior to give the appearance that the cell is empty. You can't truly hide a single cell - either hide entire row or entire column. Do those error cells, or any of the cells without errors, contain formulas?
    Last edited by JoeMo; Jul 12th, 2019 at 08:36 PM.
    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. #15
    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

    Yeah, exactly! Didn't notice it for this reason. And yes, they do contain formulas, but these formulas generate the initial "X" term searched for with the macro, so they need to stay there. The code works just fine regardless, it's just that as soon as it recognizes an error cell, the code indicates the mismatch error.

    Quote Originally Posted by JoeMo View Post
    I assume that means the format is chosen such that if an error is present the cell font is set to match the cell interior to give the appearance that the cell is empty. You can't truly hide a single cell - either hide entire row or entire column. Do those error cells, or any of the cells without errors, contain formulas?

  6. #16
    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
    Yeah, exactly! Didn't notice it for this reason. And yes, they do contain formulas, but these formulas generate the initial "X" term searched for with the macro, so they need to stay there. The code works just fine regardless, it's just that as soon as it recognizes an error cell, the code indicates the mismatch error.
    Since there was no mention of formulas in your OP, I have assumed there were none from the outset. The code I provided was designed on that assumption. It's very fast, but to enable that speed it puts the value of every cell into an array in memory, processes the array (in memory) and writes the modified array back to the original range as values. In other words, it replaces any formulas with their values. i'm surprised you haven't noticed that.

    If you want to preserve the formulas, I need to approach the task differently. But to do that without further unpleasant surprises, I would like to work with your table. Can you post a representative portion of it, sensitive information modified if necessary? You can use one of the tools in the link below.
    https://www.mrexcel.com/forum/about-...tachments.html
    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. #17
    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,

    Yeah I realized that the formulas were replaced with values, but I figured that wasn't a huge problem. I don't like asking for too much, so I figured I could modify the code myself if I needed to, but then I realized this is too advanced for a beginner like me. I figure I should have asked in the beginning to avoid being such a pain, I'm sorry for that.

    As for the table, I would gladly provide you with it, but I do not have access to the HTML Maker, Add-ins and whatnot, as they are blocked by my work's firewall and internet security settings. I'm surprised I even have access to this website.

    However, the table's formulas are rather simple, and I can explain them:
    Basically, a task that needs to be done during a certain week is marked with an "X", so I have a calendar of each numbered week (1-104) for 2019-2020 (columns L-DK, hence the cells for the macro to work in).
    1. Col (J) has a recurrence number that tells us how often the task needs to be done (in weeks), hence the number of columns between each "X" in the row.
    2. Col (DM) simply has a DATEDIF function that says what week number the very first task of this sort is to be done in, hence the original "X" to search for. This is why I need to add "X" every "Col (J)" number of columns after this first mark.

    The functions in cells L11:DK185 are as follows : =IF([@Column65]=(WEEK NUMBER HERE),"X","") , where Column65 is Col (DM).
    So basically, if the value of Col (DM) (i.e. 65) matches that of a week number within the formulas of L11:DK185, the first "X" pops up in this row's cell, and "X" needs to be inserted every "Col (J)" number of weeks after that.

    That is why the cells have errors in them sometimes, because if Col (DM) is empty in a certain row, there is a calculation error in all cells from L:DK within this row.

    That is all! I doubt any other surprises can come up now. Thank you for taking the time, and I'm sorry again I didn't mention off the bat that cell values should be preserved and/or error cells ignored.

    Have a nice one day,
    Jonathan

    Quote Originally Posted by JoeMo View Post
    Since there was no mention of formulas in your OP, I have assumed there were none from the outset. The code I provided was designed on that assumption. It's very fast, but to enable that speed it puts the value of every cell into an array in memory, processes the array (in memory) and writes the modified array back to the original range as values. In other words, it replaces any formulas with their values. i'm surprised you haven't noticed that.

    If you want to preserve the formulas, I need to approach the task differently. But to do that without further unpleasant surprises, I would like to work with your table. Can you post a representative portion of it, sensitive information modified if necessary? You can use one of the tools in the link below.
    https://www.mrexcel.com/forum/about-...tachments.html

  8. #18
    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

    Seems you want to put an "X" in cells that have formulas in them which are not returning an "X". That's not possible if you want to retain the formulas.
    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. #19
    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

    Alright, well if it is possible to make the code work and not retain the formulas that's fine! It just needs to neglect the error cells and I'd be 100% satisfied with that.

    Otherwise, if you think the above would be too long to script, I can just paste the values in another document, run the code there, then recopy the values in the original document. Because your code works wonders, the problem is moreso my document.

    Thanks

  10. #20
    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
    Alright, well if it is possible to make the code work and not retain the formulas that's fine! It just needs to neglect the error cells and I'd be 100% satisfied with that.

    Otherwise, if you think the above would be too long to script, I can just paste the values in another document, run the code there, then recopy the values in the original document. Because your code works wonders, the problem is moreso my document.

    Thanks
    This will ignore the error cells, add the X's and replace all formula cells with either: (1) the value or error the cells were returning or (2) X if the cell is positioned to meet the spacing set by the col J value.
    Code:
    Sub chirolove()
    Dim R As Range, Vin As Variant, Y As Variant, i As Long, j As Long, k As Long, Z As Variant
    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 Not IsError(Vin(i, j)) Then
                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
            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!

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
  •