Forum Discussion

Dfuhrman8's avatar
Dfuhrman8
Copper Contributor
May 02, 2022

Excel - VBA Help - Two Sheets matching amounts

I have two sheets that I need to compare from each other.  On sheet 1, in column CR, I have a column of amounts, that I need to verify if all are listed, compare to Sheet 2, columns, S-AC. there are ...
excel
Macros and VBA
HansVogelaar's avatar
HansVogelaar
MVP
May 02, 2022

Dfuhrman8 

Perhaps change

 

ActiveCell.FormulaR1C1 = "=COUNTIF(Sheet3!S:AC, Sheet4!CR)>0"

 

to

 

ActiveCell.Formula = "=COUNTIF(Sheet3!S:AC,CR1)=COUNTIF(CR:CR,CR1)"

Dfuhrman8's avatar
Dfuhrman8
Copper Contributor
May 02, 2022
That did not work. It only worked for some of the cells, but did not count on all of them.
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 02, 2022

    Dfuhrman8 

    Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        May 02, 2022

        Dfuhrman8 

        Your request in the first post was "I need to make sure if for example, $15.00 is listed 4 times on sheet 1, then on sheet 2 $15.00 is listed four times."

        That suggests creating a rule for column A on Sheet1.

        Now you write "I need to verify that all the amounts from sheet 2 are on sheet 1."

        That suggests creating a rule for Sheet2.

        Which is it?

Resources