Referring to a variable range in COUNTIF using VBA

[aryaden]

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 range but I am having difficulty in being able to do so as I am fairly new to VBA.

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

I am trying to use the StartRow and EndRow variables as the code above is in a loop and my r values will be changing
Thanks in advance for any help!

 

[Joe4]

You have named your variables "StartRow" and "EndRow", but by using "Cells", you are trying to set them to ranges, not row numbers.
If you intend to set them to ranges, you have to use the "Set" command, i.e.

Set StartCell = Cells(r + 1, lc)
Set EndCell = Cells(r + counter, lc)

However, maybe this is what you really mean:

 Set rng = Range(Cells(r+1, lc), Cells(r+counter, lc))
 Range(Cells(r, "C"), Cells(r, lc)).Formula = "=IF(COUNTIF(" & rng.Address & ", ""X""), ""X"", "" "")"

 

[aryaden]

You have named your variables "StartRow" and "EndRow", but by using "Cells", you are trying to set them to ranges, not row numbers.
If you intend to set them to ranges, you have to use the "Set" command, i.e.

Set StartCell = Cells(r + 1, lc)
Set EndCell = Cells(r + counter, lc)

However, maybe this is what you really mean:

 Set rng = Range(Cells(r+1, lc), Cells(r+counter, lc))
 Range(Cells(r, "C"), Cells(r, lc)).Formula = "=IF(COUNTIF(" & rng.Address & ", ""X""), ""X"", "" "")"

Thanks! The code you sent using "Set rng" is what I meant.

When I use the code you provided the row numbers are changing and working as intended but the column letter is E (the last column) for all of them instead of it changing from B -> C -> D....etc when the column changes. Is there anyway to change this so the column references change?

 

[Joe4]

When I use the code you provided the row numbers are changing and working as intended but the column letter is E (the last column) for all of them instead of it changing from B -> C -> D....etc when the column changes. Is there anyway to change this so the column references change?

I am having a hard time making sense of this.
Can you walk me through actual examples?
Let me know the address of a few of these formulas, and what the formulas should be for each cell.

 

[aryaden]

I am having a hard time making sense of this.
Can you walk me through actual examples?
Let me know the address of a few of these formulas, and what the formulas should be for each cell.

This is what the current code looks like

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 celected cell range and define range for COUNTIF ***New Code from Joe4
            Set Rng = Range(Cells(r + 1, lc), Cells(r + counter, lc))
            Range(Cells(r, "C"), Cells(r, lc)).Formula = "=IF(COUNTIF(" & Rng.Address & ", ""X""), ""X"", "" "")"
            'Range(Cells(r, "C"), Cells(r, lc)).Formula = "=IF(COUNTIF(C4:C5, ""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

After running the code, the output is:

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

Cell Formulas
Range	Formula
B3:E3	B3	=IF(COUNTIF($E$4:$E$5, "X"), "X", " ")
B6:E6	B6	=IF(COUNTIF($E$7:$E$9, "X"), "X", " ")
B10:E10	B10	=IF(COUNTIF($E$11:$E$12, "X"), "X", " ")

The cell ranges with formulas are B3:E3, B6:E6, and B10:E10.

For cells B3:E3 the formula in all of them is =IF(COUNTIF($E$4:$E$5, "X"), "X", " ")

My goal is for it to be:
B3 -> =IF(COUNTIF($B$4:$B$5, "X"), "X", " ")
C3 -> =IF(COUNTIF($C$4:$C$5, "X"), "X", " ")
D3 -> =IF(COUNTIF($D$4:$D$5, "X"), "X", " ")
E3 -> =IF(COUNTIF($E$4:$E$5, "X"), "X", " ")
And the same pattern applied to B6:E6 and B10:E10.

Thanks for all your time!

 

[Joe4]

OK, I see. Try this:

    Set rng = Range(Cells(r + 1, "B"), Cells(r + counter, "B"))
    Cells(r, "B").Formula = "=IF(COUNTIF(" & rng.Address(0, 0) & ", ""X""), ""X"", "" "")"
    Cells(r, "B").Copy Range(Cells(r, "C"), Cells(r, lc))

 

[aryaden]

OK, I see. Try this:

    Set rng = Range(Cells(r + 1, "B"), Cells(r + counter, "B"))
    Cells(r, "B").Formula = "=IF(COUNTIF(" & rng.Address(0, 0) & ", ""X""), ""X"", "" "")"
    Cells(r, "B").Copy Range(Cells(r, "C"), Cells(r, lc))

That worked perfectly! Thank you!

 

[aryaden]

I was losing the titles (Basketball, Tennis, Soccer) but I shifted "B"s to "C"s and the "C"s to "D"s and it seemed to fix the issues!

 

[Joe4]

Excellent! Glad it all worked out for you.