We help IT Professionals succeed at work.

How to set a range for looping

carlosab
carlosab asked
on

I need to run a For Each x in Range command where the Range is the block of cells that contains information. I think that I need some variation or combination of these two commands, but I don't know how to put them together:

    Range("A2").End(xlDown).Offset(1, 0).Select

    For Each x In Range
Comment
Watch Question

Top Expert 2011

Commented:
Assuming cel a2 to the relevant offset in column 2 then try:

Dim cel As Range
For Each cel In Range("a2:A" & Range("A2").End(xlDown).Offset(1, 0).Row)
'...
Next

Chris

Author

Commented:
I still can't get it. The code is below. The relevant columns that I need to evaluate are H:O

Sub Proper_Case()
  Sheets("Need Addresses").Select
  Application.ScreenUpdating = False

    Dim cel As Range
    For Each cel In Range("a2:A" & Range("A2").End(xlDown).Offset(1, 0).Row)
    x.Value = Application.Proper(x.Value)
    Next


' Loop to cycle through each cell in the specified range.

    Cells.Replace What:="Lvnv", Replacement:="LVNV", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Rgm", Replacement:="RGM", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Llc", Replacement:="LLC", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Ii", Replacement:="II", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="Iii", Replacement:="III", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Iv", Replacement:="IV", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
Application.ScreenUpdating = True
End Sub

Open in new window

Top Expert 2011
Commented:
LIke this?

Note I resorted Ii and Iii as otherwise the correction occurred too soon for Ii preventing Iii from taking effect.

Chris
Sub Proper_Case()
Dim cel As Range
Dim rng As Range
  
  Sheets("Need Addresses").Select
  Application.ScreenUpdating = False

    Set rng = Range("h2:o" & Range("h2").End(xlDown).Offset(1, 0).Row)
    
    For Each cel In rng
        cel = Application.WorksheetFunction.Proper(cel)
    Next


        rng.Cells.Replace What:="Lvnv", Replacement:="LVNV", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        rng.Cells.Replace What:="Rgm", Replacement:="RGM", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        rng.Cells.Replace What:="Llc", Replacement:="LLC", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        rng.Cells.Replace What:="Iii", Replacement:="III", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        rng.Cells.Replace What:="Ii", Replacement:="II", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
        rng.Cells.Replace What:="Iv", Replacement:="IV", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
    Application.ScreenUpdating = True

End Sub

Open in new window

Author

Commented:
Thanks.