Excel VBA Loop Dynamic Range

Question

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

Emmanuel POUPEAU · Accepted Answer · 2018-12-28 11:13:26Z

2

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

answered Dec 28, 2018 at 11:13

Emmanuel POUPEAU

361 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA Loop Dynamic Range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related