Evaluating and Comparing Cell Values with VBA Code

Question

I am comparing the values from column A to the values in Column B and expecting an output in Column C. Column A contains IDs. Each ID can have a Cloud or Not Cloud component. An app that is all cloud should have the output of the cloud. An app that is all not cloud should have the output of the cloud. An App with cloud and not cloud components is a hybrid

I have tried a basic excel formula comparing the range of the values in column A to the values in column B but there must be an easier way in VBA. I am new to coding but I do not believe it needs to be as complex as storing the values in an Array.

Sub CompareCells()
    If Range("AA:AA").value = Range("AA:AA").value Then
       'Not sure what to do here to evaluate after making sure the range is true
    Else
        'Not sure where to go next
    End If
End Sub

Example picture attached. The output should give the value of Not cloud, Hybrid, or Cloud depending on the disposition values of each application

This would actually be a lot easier with a formula..

CLR
– CLR

2019-09-16 15:00:18 +00:00
Commented Sep 16, 2019 at 15:00 — CLR
– CLR, Commented Sep 16, 2019 at 15:00
The list will always be sorted by the ID?

Damian
– Damian

2019-09-16 15:00:27 +00:00
Commented Sep 16, 2019 at 15:00 — Damian
– Damian, Commented Sep 16, 2019 at 15:00
Yes it will always be sorted by the ID

Ryan Coleman
– Ryan Coleman

2019-09-16 15:05:07 +00:00
Commented Sep 16, 2019 at 15:05 — Ryan Coleman
– Ryan Coleman, Commented Sep 16, 2019 at 15:05
@RyanColeman check my answer below.

Damian
– Damian

2019-09-16 15:05:48 +00:00
Commented Sep 16, 2019 at 15:05 — Damian
– Damian, Commented Sep 16, 2019 at 15:05

CLR · Accepted Answer · 2019-09-16 15:22:03Z

1

This would work, if entered into C2 (assuming above are in A and B) and copied down:

=IF(COUNTIFS(A:A,A2,B:B,"Cloud")=0,"Not Cloud",IF(COUNTIFS(A:A,A2,B:B,"Not Cloud")=0,"Cloud","Hybrid"))

And in R1C1 style if preferred:

=IF(COUNTIFS(C[-2],RC[-2],C[-1],"Cloud")=0,"Not Cloud",IF(COUNTIFS(C[-2],RC[-2],C[-1],"Not Cloud")=0,"Cloud","Hybrid"))

So if you were looking to create some automation for this you could use something like this:

With ActiveSheet

    .Range("C2:C12").FormulaR1C1 = _
        "=IF(COUNTIFS(C[-2],RC[-2],C[-1],""Cloud"")=0,""Not Cloud"",IF(COUNTIFS(C[-2],RC[-2],C[-1],""Not Cloud"")=0,""Cloud"",""Hybrid""))"
    ActiveSheet.Calculate
    .Range("C2:C12").Value = .Range("C2:C12").Value

End With

edited Sep 16, 2019 at 15:22

answered Sep 16, 2019 at 15:06

CLR

12.3k1 gold badge13 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ryan Coleman Over a year ago

The formula worked perfectly in excel. Would a similar format work for VBA?

CLR Over a year ago

Option 2 - You could write a macro that fills 'column C' with the formula and then paste-special-values it.. Option 3 - or you could recreate the formula in VBA using WorksheetFunction.SumIfs Option 4 - or you could write your own count routine - but in all cases the formula is the quickest easiest option. If you're looking to automate it, then I'd probably look at option 2 first.

Damian · Accepted Answer · 2019-09-16 15:02:28Z

0

This formula should do the trick:

=IF(IF(COUNTIFS(A:A;A2;B:B;"Cloud")>0;1;0)+IF(COUNTIFS(A:A;A2;B:B;"Not Cloud")>0;1;0)=2;"Hybrid";IF(COUNTIFS(A:A;A2;B:B;"Cloud");"Cloud";"Not Cloud"))

answered Sep 16, 2019 at 15:02

Damian

5,1741 gold badge12 silver badges22 bronze badges

Collectives™ on Stack Overflow

Evaluating and Comparing Cell Values with VBA Code

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related