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

[Chirolove]

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.

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

 

[Chirolove]

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!

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.

 

[JoeMo]

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.

 

[Chirolove]

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!

You are welcome - thanks for the reply.

 

[JoeMo]

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?

 

[Chirolove]

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.

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?

 

[JoeMo]

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-board/508133-attachments.html

 

[Chirolove]

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

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-board/508133-attachments.html

 

[JoeMo]

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.

 

[Chirolove]

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

 

[JoeMo]

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.

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