Referring to a variable range in COUNTIF using VBA

aryaden

Board Regular
Joined
Jun 9, 2021
Messages
101
Office Version
  1. 2019
Platform
  1. Windows
I have currently defined two variables(StartRow and EndRow) in my code: (counter is an integer)
VBA Code:
 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.
VBA Code:
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!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,139
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
Set StartCell = Cells(r + 1, lc)
Set EndCell = Cells(r + counter, lc)

However, maybe this is what you really mean:
VBA Code:
 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

Board Regular
Joined
Jun 9, 2021
Messages
101
Office Version
  1. 2019
Platform
  1. Windows
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.
VBA Code:
Set StartCell = Cells(r + 1, lc)
Set EndCell = Cells(r + counter, lc)

However, maybe this is what you really mean:
VBA Code:
 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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,139
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 9, 2021
Messages
101
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
VBA Code:
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
ABCDE
1Description1234
2abcd
3BasketballXXXX
4AAAXX
5BBBXX
6SoccerXXXX
7CCCCX
8DDDDX
9EEEEXX
10TennisXXXX
11FFFXX
12GGGXX
13
14
Sheet2
Cell Formulas
RangeFormula
B3:E3B3=IF(COUNTIF($E$4:$E$5, "X"), "X", " ")
B6:E6B6=IF(COUNTIF($E$7:$E$9, "X"), "X", " ")
B10:E10B10=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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,139
Office Version
  1. 365
Platform
  1. Windows
OK, I see. Try this:
VBA Code:
    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))
 
Solution

aryaden

Board Regular
Joined
Jun 9, 2021
Messages
101
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

OK, I see. Try this:
VBA Code:
    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

Board Regular
Joined
Jun 9, 2021
Messages
101
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,139
Office Version
  1. 365
Platform
  1. Windows
Excellent! Glad it all worked out for you.
 

Forum statistics

Threads
1,148,283
Messages
5,745,842
Members
423,981
Latest member
ph1l

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top