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

 

[JoeMo]

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.

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

 

[Chirolove]

Hey JoeMo,

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

        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.

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.

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

 

[JoeMo]

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

 

[Chirolove]

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,

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

 

[JoeMo]

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?

 

[Chirolove]

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,

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?

 

[JoeMo]

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.

 

[Chirolove]

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...

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.

 

[JoeMo]

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.