Loop Through Set Range & Run Macro for Every Column in Range

How How · Jan 5th 2018

Hi, I am working on a project and am stuck on a problem regarding entering "Bye's" into a set range of letters. The bye's position will be determined row 22 of the column. I have created a macro that will do it individually but it would be a lot of codes for all the separate columns. I have attached an example which will hopefully show what I'm trying to achieve. I know I could enter the byes manually but.... That wouldn't be as much fun..... + I hope to do a larger project once I have the small one working properly. P.s The code is in the example sheet

nilem · Jan 5th 2018

Hi HowHow,
try this

Code

Sub ByeForA()
Dim x, y(), Weeks, i&, j&, k&


x = Range("H2:X19").Value
ReDim y(1 To UBound(x) + 1, 1 To UBound(x, 2))
Weeks = Range("H22:X22").Value


For j = 1 To UBound(Weeks, 2)
    If Len(Weeks(1, j)) Then
        k = 0
        For i = 1 To UBound(x)
            k = k + 1
            If i = Weeks(1, j) Then
                y(k, j) = "Bye"
                k = k + 1: y(k, j) = x(i, j)
            Else
                y(k, j) = x(i, j)
            End If
        Next i
    End If
Next j


Range("H2:X20").Value = y
End Sub

Display More

How How · Jan 6th 2018

Nilem, Yup that works very well... I wont pretend to full understand the code but I guess I will have to have a play with it in order to get my head round it.

Thank you very much!!

Loop Through Set Range & Run Macro for Every Column in Range

Participate now!

Share