How to Center Formula Result Cell in VBA

[aryaden]

I currently have this code which puts an X in a selected range based on a COUNTIF Formula. I would like these Xs to be centered in the cells

Set Rng = Range(Cells(r + 1, "C"), Cells(r + counter, "C"))
Cells(r, "C").Formula = "=IF(COUNTIF(" & Rng.Address(0, 0) & ", ""X""), ""X"", "" "")"
Cells(r, "C").Copy Range(Cells(r, "D"), Cells(r, lc))
Application.CutCopyMode = False

Currently I have tried: Rng.HorizontalAlignment = xlCenter

Thank you for the help in advance!

 

[johnnyL]

I think you are gonna need to provide a bit more code than that.

 

[aryaden]

I think you are gonna need to provide a bit more code than that.

This is the post/code I am referring to:

Referring to a variable range in COUNTIF using VBA

I have currently defined two variables(StartRow and EndRow) in my code: (counter is an integer) StartRow = Cells(r + 1, lc) EndRow = Cells(r + counter, lc) I would like to replace the input range I currently have as "C4:C5" with syntax that allows me to refer to StartRow and EndRow as the...

www.mrexcel.com

Thank you again for the help!

 

[johnnyL]

Off the top, I would say you might try:

Range("C:D").EntireColumn.HorizontalAlignment = xlCenter

 

[aryaden]

I think you are gonna need to provide a bit more code than that.

This is the fixed code from that post that works :

Sub Integrate()

    Dim lr As Long
    Dim r As Long
    Dim lc As Long
    Dim counter As Integer
   
    Application.ScreenUpdating = False
   
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
   
'   Loop through data backwards
    For r = lr To 3 Step -1
'   Create counter
    counter = counter + 1

'       Check to see if column A is not equal to row above it
        If (Cells(r, "A") <> "") And (Cells(r, "A") <> Cells(r - 1, "A")) Then
'           Insert blank row
            Rows(r).Insert
'           Copy value to column B
            Cells(r, "B") = Cells(r + 1, "A")
'           Copy formatting
            Cells(r + 1, "A").Copy
            Range(Cells(r, "B"), Cells(r, lc)).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False        
'           Enter formula in selected cell range and define range for COUNTIF ***New Code from Joe4
          Set rng = Range(Cells(r + 1, "C"), Cells(r + counter, "C"))
          Cells(r, "C").Formula = "=IF(COUNTIF(" & rng.Address(0, 0) & ", ""X""), ""X"", "" "")"
          Cells(r, "C").Copy Range(Cells(r, "D"), Cells(r, lc))
            Application.CutCopyMode = False
            ' Set counter = 0 for next set
            counter = 0
        End If
    Next r
   
'   Delete column A
    Columns("A:A").Delete
'   Autofit columns
    Cells.EntireColumn.AutoFit
    Application.ScreenUpdating = True
   
End Sub

 

[aryaden]

Off the top, I would say you might try:

Range("C:D").EntireColumn.HorizontalAlignment = xlCenter

I am trying to get all columns until "lc" to be centered. Thank you!

 

[johnnyL]

Again off the top:

Range("C:" & lc).EntireColumn.HorizontalAlignment = xlCenter

 

[aryaden]

Again off the top:

Range("C:" & lc).EntireColumn.HorizontalAlignment = xlCenter

I am getting an error having to do with Global variables

 

[johnnyL]

yes, I know, Lemme play a bit to see what is going on.

 

[aryaden]

yes, I know, Lemme play a bit to see what is going on.

Thank you very much!

Best,
Arya