error 438 didn't occur yesterday... why today? or even at all?

[jbaich]

Hey all, I'm getting Run-time error 438 "Object doesn't support this property or method" in my code, which i didn't get yesterday and I'm not sure why I'm getting at all... It's occuring on the

.Horizontal Alignment = xlRight

line in the section of code below...

       With Template.Sheets("Forms")
    
          .Range("G13") = FILEREF
            
        For i = 0 To RowCount
          .Range("A" & 23 + i & ":B" & 23 + i).MergeCells = True
          .HorizontalAlignment = xlRight
          .NumberFormat = "@"
          .Range("D" & 23 + i & ":E" & 23 + i).MergeCells = True
          .HorizontalAlignment = xlCenter
          .NumberFormat = "@"

Can anyone explain this to me? I see in the properties explorer window that .HorizontalAlignment is a propert of Range... I'll admit I don't have the best handle on this higher level objects, classes, methods stuff yet...

Thanks,
Joe

 

[Jonmo1]

You need to have a range object on the HorizontalAlignment property

I'd guess
.Range("A" & 23 + i & ":B" & 23 + i).HorizontalAlignment = xlRight


Same for the NumberFormat

 

[jbaich]

Thanks for quick reply! is it possible to do something like below? This formating code is repeated for several different ranges so I'm wondering if I can make it as concise as possible?

with Range
.HorizontalAlignment = xlRigh
.NumberFormat = "@"

larger section of code:

       With Template.Sheets("Forms")
    
          .Range("G13") = FILEREF
            
        For i = 0 To RowCount
        
          .Range("A" & 23 + i & ":B" & 23 + i).MergeCells = True
          .HorizontalAlignment = xlRight
          .NumberFormat = "@"
          .Range("D" & 23 + i & ":E" & 23 + i).MergeCells = True
          .HorizontalAlignment = xlCenter
          .NumberFormat = "@"
          
          .Range("F" & 23 + i & ":G" & 23 + i).MergeCells = True
          .HorizontalAlignment = xlLeft
          .Format = "$#,##0"
          .Range("H" & 23 + i & ":J" & 23 + i).MergeCells = True
          .HorizontalAlignment = xlLeft
          .Format = "$#,##0"
          .Range("K" & 23 + i & ":K" & 23 + i).MergeCells = True
          .HorizontalAlignment = xlRight
          .NumberFormat = "@"
          .Range("L" & 23 + i & ":M" & 23 + i).MergeCells = True
          .HorizontalAlignment = xlCenter
          .NumberFormat = "@"
          
          .Range("N" & 23 + i & ":O" & 23 + i).MergeCells = True
          .HorizontalAlignment = xlCenter
          .NumberFormat = "@"
          .Range("P" & 23 + i & ":Q" & 23 + i).MergeCells = True
          .HorizontalAlignment = xlLeft
          .Format = "$#,##0"
          .Range("R" & 23 + i & ":U" & 23 + i).MergeCells = True
          .HorizontalAlignment = xlLeft
          .Format = "$#,##0"


          .Range("A" & 23 + i) = "Class"
          .Range("C" & 23 + i) = Master.Sheets("Original Values").Range("O" & OriginalRow + i).Value
          .Range("D" & 23 + i) = Master.Sheets("Original Values").Range("Q" & OriginalRow + i).Value
          .Range("F" & 23 + i) = Master.Sheets("Original Values").Range("S" & OriginalRow + i).Value
          .Range("H" & 23 + i) = Master.Sheets("Original Values").Range("T" & OriginalRow + i).Value
          .Range("k" & 23 + i) = "Class"
          .Range("L" & 23 + i) = Master.Sheets("Original Values").Range("O" & OriginalRow + i).Value
          .Range("N" & 23 + i) = Master.Sheets("Original Values").Range("Q" & OriginalRow + i).Value
            
            Next


           .Range("F" & 23 + RowCount + 1).Formula = "=SUBTOTAL(9,F23:D" & 23 + RowCount & ")"
           .Range("H" & 23 + RowCount + 1).Formula = "=SUBTOTAL(9,H23:H" & 23 + RowCount & ")"
           .Range("E19") = Roll
           


    End With

Thanks,
Joe

 

[mole999]

have you tried centreacrossselection rather than merging cell, ok you you can't do left or right alignment

 

[Jonmo1]

Yes, that's what the With Structure is for.

Looks like you already have a with going for the Sheet.
So you can now 'Nest' another with for the range on that sheet.

Something like this

With Template.Sheets("Forms")
    .Range("G13") = FILEREF
    
    With .Range("A" & 23 + i & ":B" & 23 + i)
        For i = 0 To RowCount
        
        .MergeCells = True
        .HorizontalAlignment = xlRight
        .NumberFormat = "@"
    End With
    
    With .Range("D" & 23 + i & ":E" & 23 + i)
        .MergeCells = True
        .HorizontalAlignment = xlCenter
        .NumberFormat = "@"
    End With
End With

 

[jbaich]

Thanks guys, I'd been trying that but must have been missing something, seems to be working now though!

Cheers!
Joe

 

[Jonmo1]

You're welcome.