Entering a Formula into a Range in VBA

[aryaden]

Currently my code has these initial components

Dim r As Long
Dim lr As Long
Dim lc As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
 r = lr

I am trying to use this line but I am getting an error

Range(Cells(r, "C"), Cells(r, lc)).Formula = "=IF(COUNTIF(C4:C5,"X"),"X","")"

I am unsure if I am referencing the Range incorrectly or if there is something wrong with my formula

If I want the cells referenced within COUNTIF to change according to column should I ".Select" the intended Range and then use "FillDown"?

Thanks for the help in advance!

 

[Joe4]

The issue is that your formula has double-quotes in it, and double-quotes are also used as text qualifiers in VBA code, so you need to double them up.
It can be a bit confusing to figure out how to write it, but the good news is that you do not need to, you can let Excel figure it out.
Simply turn on the Macro Recorder, and record yourself entering the formula in the first cell. Then stop the Macro Recorder and view your recorded code.
You can copy this formula and paste it into your existing VBA code.

You do not need to use "FillDown", as we are applying this code to the whole range at once.

 

[aryaden]

The issue is that your formula has double-quotes in it, and double-quotes are also used as text qualifiers in VBA code, so you need to double them up.
It can be a bit confusing to figure out how to write it, but the good news is that you do not need to, you can let Excel figure it out.
Simply turn on the Macro Recorder, and record yourself entering the formula in the first cell. Then stop the Macro Recorder and view your recorded code.
You can copy this formula and paste it into your existing VBA code.

You do not need to use "FillDown", as we are applying this code to the whole range at once.

Thank you! Just tried the Macro Recorder approach and it is very useful

 

[Joe4]

Thank you! Just tried the Macro Recorder approach and it is very useful

You are welcome.
Yep, it is a useful tool for getting snippets of code.
Even us "pros" use it!

 

[aryaden]

You are welcome.
Yep, it is a useful tool for getting snippets of code.
Even us "pros" use it!

I am still getting an error when using

Range(Cells(r, "C"), Cells(r, lc)).Formula = "=IF(COUNTIF(C4:C5,""X""),""X"","")"

If I take out the "=" before the IF and use:

Range(Cells(r, "C"), Cells(r, lc)).Formula = "IF(COUNTIF(C4:C5,""X""),""X"","")"

the cell range I define has the text "IF(COUNTIF(C4:C5,""X""),""X"","")" in each cell but it not as a formula

I wasn't sure if it was because the C4:C5 was causing issues so I tried it with "=2+2" and the same thing happens where there is an error

 

[Joe4]

Where exactly is the first cell that you are posting this formula?
What is the cell address?

 

[aryaden]

Where exactly is the first cell that you are posting this formula?
What is the cell address?

Thank you for all the help so far!

I am trying to post this formula within an if loop where the cell address would change every time it enters the loop. My goal is to use the COUNTIF function and define the COUNTIF logic test range as StartRow : EndRow, I currently just have C4:C6 as proxy to see if I can get it to work in general

This is all of the code currently, It is code that you provided me in regards to another post I made and I am trying to edit it to add further functionalities:

Sub Integrate()
    Dim lr As Long
    Dim r As Long
    Dim lc As Long
    Dim counter As Integer
    Dim StartRow As Long
    Dim EndRow As Long
    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
'           Define Ranges for COUNTIF
            StartRow = Cells(r + 1, lc)
            EndRow = Cells(r + counter, lc)
'           Enter formula in selected cell range: *** Does Not Work***
            Range(Cells(r, "C"), Cells(r, lc)).Formula = "=IF(COUNTIF( C4:C6, ""X""), ""X"", "")"
            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

And this is the worksheet I am working with:

Book1345.xlsm
	A	B	C	D	E	F
1	Category	Description	1	2	3	4
2			a	b	c	d
3	Basketball	AAA	X			X
4	Basketball	BBB		X	X
5	Soccer	CCCC				X
6	Soccer	DDDD				X
7	Soccer	EEEE	X		X
8	Tennis	FFF		X		X
9	Tennis	GGG		X		X
10
Original_1

 

[Joe4]

It appears that you didn't copy the recorded formula completely, and only addresses the double-quotes around the "X" and not the ones at the end.
It should look something like:

.Formula = "=IF(COUNTIF(C4:C6,""X""),""X"","""")"

 

[aryaden]

Thanks for catching that!