PDA

View Full Version : Why is my alignment vba code not working.



lucpian
04-10-2008, 03:12 PM
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.


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


Thanks

Lucpian

mdmackillop
04-10-2008, 03:43 PM
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
04-10-2008, 06:26 PM
Have you tried casting AligningCells as a Sub? Functions return values, asking them to modify the environment is not dependable.

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

tstav
04-11-2008, 12:12 AM
Check for
On Error Resume Next
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
04-11-2008, 11:02 AM
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
04-11-2008, 11:05 AM
Why not post your workbook with sample data?

MikeO
04-11-2008, 11:10 AM
You have this in your code:
rowcount = Range("A65536").End(xlUp).Row

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

mdmackillop
04-11-2008, 11:26 AM
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
04-11-2008, 01:29 PM
Thanks, but It is really frustrating because non of my vba code seems to be working. Please, help me out.

Lucpian

mdmackillop
04-11-2008, 01:31 PM
It works for me. Have you read any of my posts, or am I wasting my time here?

figment
04-11-2008, 02:59 PM
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.

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