CODEWITHSUNDEEP
excel
Itachi_Uchiha
Itachi_Uchiha

Reputation: 19

Excel VBA Loop Dynamic Range

I'm wanting to analyse the cells in column B based on the length of column C and return my values to column A.

The following code works great for sheet1, however in subsequent sheets the range doesn't change to reflect the length of column C. In other words, for all subsequent sheets though the values returned are correct, they are only populated up to the length of column C in sheet1.

Anyone know why my loop is not working to reflect the dynamic range of column C, please?

Sub SetValuesAllSheets()
    Dim wSht As Worksheet
    Dim myRng As Range
    Dim allwShts As Sheets
    Dim cel As Range

    Dim LR As Long
    LR = Range("C" & Rows.Count).End(xlUp).Row


    Set allwShts = Worksheets
    For Each wSht In allwShts
        Set myRng = wSht.Range("B1:B" & LR)
        For Each cel In myRng
            If cel.Interior.Color = RGB(255, 0, 0) Then
                cel.Offset(0, -1) = "colour"
            ElseIf IsNumeric(cel) = True Then
                cel.Offset(0, -1) = "number"
            End If
        Next cel
    Next wSht
End Sub

Upvotes: 0

Views: 57

Answers (1)

Emmanuel POUPEAU
Emmanuel POUPEAU

Reputation: 36

Indeed, LR is determined for the active sheet. You need to insert it in the loop to get it work:

Sub SetValuesAllSheets()
    Dim wSht As Worksheet
    Dim myRng As Range
    Dim allwShts As Sheets
    Dim cel As Range

    Dim LR As Long

    Set allwShts = Worksheets
    For Each wSht In allwShts
        LR = wSht.Range("C" & Rows.Count).End(xlUp).Row
        Set myRng = wSht.Range("B1:B" & LR)
        For Each cel In myRng
            If cel.Interior.Color = RGB(255, 0, 0) Then
                cel.Offset(0, -1) = "colour"
            ElseIf IsNumeric(cel) = True Then
                cel.Offset(0, -1) = "number"
            End If
        Next cel
    Next wSht
End Sub

Regards

Upvotes: 2

Related Questions