Why is my alignment vba code not working.

[lucpian]

Hi,

I wrote a vba code that should basically check and see if there any cell that is not left aligned, and then aligned it. I am not getting any error, but it is not working either, and I have spent almost the entire day trying to figure out why it is not working. Here is the code, and I need the help of members of this forum.

[VBA]
Function Aligningcells(columnname As Integer)
Dim rowcount
Dim R
Dim Cells
rowcount = Range("A65536").End(xlUp).Row
For R = 2 To rowcount
If Sheet1.Cells(R, columnname).HorizontalAlignment = xlRight Then
' MsgBox "The cell(s) highlighted in blue is/are not align "
Sheet1.Cells(R, columnname).HorizontalAlignment = xlLeft
End If

If Sheet1.Cells(R, columnname).HorizontalAlignment = xlCenter Then
'MsgBox "The cell(s) highlighted in blue is/are not align "
Sheet1.Cells(R, columnname).HorizontalAlignment = xlLeft
End If

Next
End Function

Sub CheckFieldsAlignment()
Aligningcells (1)
Aligningcells (2)
Aligningcells (3)
Aligningcells (4)
Aligningcells (5)
Aligningcells (6)
Aligningcells (7)
Aligningcells (8)
Aligningcells (9)
Aligningcells (10)
Aligningcells (11)
Aligningcells (12)
Aligningcells (13)
Aligningcells (14)
Aligningcells (15)
Aligningcells (16)
Aligningcells (17)
Aligningcells (18)
Aligningcells (19)

End Sub
[/VBA]

Thanks

Lucpian

[mdmackillop]

It could be that you are using Styles unknowingly. For example, if you set a number to Comma style, it will not show Left or Centre alignment.

[mikerickson]

Have you tried casting AligningCells as a Sub? Functions return values, asking them to modify the environment is not dependable.

[VBA]Dim Cells[/VBA]is possibly part of the problem. Confusion between your variable Cells and the property .Cells might be what is going on

[tstav]

Check for
[vba]On Error Resume Next[/vba]
having been set prior to calling either the sub or the function. It must be suppressing the "Type mismatch" error message you'd be getting by the conflict of DIM Cells and the .Cells property (as mikerickson has stated).

PS. Remove the Dim Cells.

[lucpian]

Thanks all. I have taken out the Dim Cells, and even added On Error Resume Next, but it is still not working and I am not getting any error either. Please, I need your help.

Lucpian

[mdmackillop]

Why not post your workbook with sample data?

[MikeO]

You have this in your code:
[vba]rowcount = Range("A65536").End(xlUp).Row[/vba]

That works as long as Column A has the most cells of data. Maybe you meant this:
[vba]rowcount = Sheet1.Cells(65536, columnname).End(xlUp).Row[/vba]

[mdmackillop]

I wrote a vba code that should basically check and see if there any cell that is not left aligned, and then aligned it.

Your code specifically does not do this. It is written to change the alignment of Right and Centre aligned cells only. By default in my basic template, cells have no preset alignment, so your code does nothing.

[lucpian]

Thanks, but It is really frustrating because non of my vba code seems to be working. Please, help me out.

Lucpian

[mdmackillop]

It works for me. Have you read any of my posts, or am I wasting my time here?

[figment]

i think your over complicating things, why not just set the entire colume to be left justifyed. unless there is a reason to go one cell at a time.

[VBA]Function Aligningcells(columnname As Integer)
Worksheets("Sheet1").Range(Cells(1, columnname), Cells(65536, columnname)).HorizontalAlignment = xlLeft
End Function[/VBA]