Using concatenated Column and Row in a formula

I am new to VBA programming with past programming experience in other languages. I have been stymied by what I thought was a fairly simple requirement.

I receive a very large spreadsheet of sales data on a monthly basis. The spreadsheet includes about 50 columns and I only need about 12-15 of them. The client is regularly adding stores, so each data file includes a variable number of stores and sales managers. I need to generate a report showing MTD performance for each store and sales manager.

I have already written the VBA code to get the file, “massage” and reformat the data. It also identifies the number of stores, unique Store names and number of Managers and unique Manager names.

I have also created the VBA code to generate the report format. See attachment for details.

I am trying to write the VBA code to automate the report generation. It can be easily created manually by entering COUNTIF and SUMIF statements. In order to automate the generation, I am trying to loop through the large data file as follows:

Dim nstores As Long
Dim nmgrs As Long
rw As Long
ctr As Long

'Get the number of stores and managers from the data table

nstores = Range(“Master!AT20”)
nmgrs = Range(“Master!AT21”)

'Set the report row number
rw = 11


'Loop through to build Store rows Units column
for ctr = 1 to nstores
Range(“B” & rw).Activate
ActiveCell.Formula = (“COUNTIF(Master!A:A,Report!A & rw”)
rw = rw + 1
Next

I get a Run-time error ‘1004’: Application-defined or object-defined error.

Is the error because I am using a concatenated column and row “A” & rw or is my syntax wrong? I get the same error when I use COUNTIFS, SUMIF, SUMIFS. I have “learned” VBA by googling commands, but I haven’t been able to figure out what to google to debug this.

Any help would be appreciated.

forum.ozgrid.com/index.php?attachment/1233918/

Thank you!

I have uploaded a small sample.

Thank you, it was very helpful. Just curious, how do you determine the sheet number of a tab? It doesn't look like the worksheet number option isn't available in Excel 2019.

I appreciate your assistance.