Proper syntax for horizonal alignment within a column

[pelkbc5172]

Hi Board members;

I haven't been coding for a while now and have been frustrated by the above topic. I have queried on the web numerous times today using "Excel VBA" followed by a space, with various topic queries and have made progress but the code I last found for horizontal alignment isn't working and I have certainly tried to use other approaches I found on the web and within my previous macros to solving the problem but they didn't work. While stepping through the code shown below using the F8 key, I can see column R - (offset,01) from Col Q - being populated properly but the Horizontal Alignment within Column R isn't working. I have gotten stuck at this point and would greatly appreciate some help achieving horizonal alignment within column R.

Option Explicit

Dim q, ColQ As Range, ThisWorkbook, WSA As Worksheet, Dept, FndSrc, BCacct As String, LastRowA As Integer

Public Sub ProcessColQ()

Set WSA = Worksheets("2024 Easter Seals Adults, Pont")
LastRowA = Cells(Rows.Count, 1).End(xlUp).Row

Set ColQ = WSA.Range(Cells(2, 17).Address, Cells(LastRowA, 17).Address) 'Contains Full String A/C Number

For Each q In ColQ 'Contains the String A/C

FndSrc = Mid(q, 4, 3) 'Fund Source
Dept = Mid(q, 8,,4)    'Department Number
BCacct = Right(q,5)  'The 5 digit Business Central (GL) Account

If BCacct = "80835" Then
   q.Offset(0, 1) = "CCBHC - Medicaid"
ElseIf BCacct = "80031" Then
   q.Offset(0, 1) = "Medicaid - SUD - Outpatient"
ElseIf BCacct = "80032" Then
   q.Offset(0, 1) = "Medicaid - SUD - Case Mgmt"
ElseIf BCacct = "80837" Then
   q.Offset(0, 1) = "TCM"
ElseIf BCacct = "80949" Then
   q.Offset(0, 1) = "Child Autism"
ElseIf BCacct = "80845" Then
   q.Offset(0, 1) = "CCBHC - HMP"
ElseIf BCacct = "80855" Then
   q.Offset(0, 1) = "CCBHC Non-Medicaid"

End If

With WSA.Range("R1")
   .EntireColumn.AutoFit
   .HorizontalAlignment = xlCenter
End With

Next q

End Sub

Thanks in advance.

Brian

 

[John_w]

Delete:

With WSA.Range("R1")
   .EntireColumn.AutoFit
   .HorizontalAlignment = xlCenter
End With

After the Next q, insert:

With WSA.Range("R1").EntireColumn
   .AutoFit
   .HorizontalAlignment = xlCenter
End With

 

[pelkbc5172]

Delete:

With WSA.Range("R1")
   .EntireColumn.AutoFit
   .HorizontalAlignment = xlCenter
End With

After the Next q, insert:

With WSA.Range("R1").EntireColumn
   .AutoFit
   .HorizontalAlignment = xlCenter
End With

Thanks so much John-w!