Web Analytics Made Easy -
StatCounter code to align data in excel - CodingForum

Announcement

Collapse
No announcement yet.

code to align data in excel

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • code to align data in excel

    Hi,

    the code below works fine for cleaning hyperlinks and whitespaces in worksheets of a excel workbook.

    Private Sub Workbook_Open()
    Dim wks As Worksheet
    Dim rngCell As Range
    For Each wks In ThisWorkbook.Worksheets
    wks.Hyperlinks.Delete
    For Each rngCell In wks.Range("A1:AA2346")
    rngCell = Replace(Application.Clean(rngCell), Chr(160), "")
    Next rngCell
    Next wks
    ThisWorkbook.Save
    End Sub

    when i add up the following to right align all data in cells , i get error "Next without For"


    Private Sub Workbook_Open()
    Dim wks As Worksheet
    Dim rngCell As Range
    For Each wks In ThisWorkbook.Worksheets
    wks.Hyperlinks.Delete
    For Each rngCell In wks.Range("A1:AA2346")
    rngCell = Replace(Application.Clean(rngCell), Chr(160), "")
    Next rngCell
    Next wks
    For Each rngCell In wks.Range("A1:AA2346")
    rngCell = HorizontalAlignment = xlRight
    Next rngCell
    Next wks
    ThisWorkbook.Save
    End Sub


    Can anybody sort this out..

    Thanks,
    Mahesh

  • #2
    indentation would help you:
    Code:
    Private Sub Workbook_Open()
      Dim wks As Worksheet
      Dim rngCell As Range
      For Each wks In ThisWorkbook.Worksheets
        wks.Hyperlinks.Delete
        For Each rngCell In wks.Range("A1:AA2346")
          rngCell = Replace(Application.Clean(rngCell), Chr(160), "")
        Next rngCell
      Next wks
    
      For Each rngCell In wks.Range("A1:AA2346")
        rngCell = HorizontalAlignment = xlRight
      Next rngCell
    
      Next wks
    
      ThisWorkbook.Save
    End Sub
    The red Next does not have a matching For.
    I am the luckiest man in the world

    Comment

    Working...
    X