(Solved) – Excel – Range in Countif changes after VBA code run


Good Day,

I am by no means a VBA / Excel expert but I do have some programming background.

I have written code that takes information from one sheet (sheet 1) and copies to another sheet (sheet 2) based on value in a cell. That works fine with no issues.

The issue I have is on a third sheet which reviews sheet two and counts based on values, the range in the countif statement changes after the script is run. It doesn’t do it every time. There are no VBA associated with sheet 3.

formula in sheet 3 is:

=COUNTIFS(Air!$A$1:$A$157,H$7, Air!$B$1:$B$157, VLOOKUP($B8,DATA!$A$2:$D$32,3,FALSE))

but changes to:

=COUNTIFS(Air!$A$1:$A$1,H$7, Air!$B$1:$B$1, VLOOKUP($B8,DATA!$A$2:$D$32,3,FALSE))

I thought that using $ would lock my range but it changes.

Can anyone help?

Leave a Reply

Your email address will not be published. Required fields are marked *