Link to home
Start Free TrialLog in
Avatar of Lennon Gary
Lennon GaryFlag for United States of America

asked on

Excel Formula converted to VBA.

I need assistance with VBA.
I need to rewrite the following excel formula into VBA.

=IF(OR(COUNTIF(car$A:$A,color!A2,COUNTIF(car$C:$C,color!A2)),”Yes”,”No”)

I have a workbook with TWO worksheets, cars & color.
the results will be in column N of the “cars” worksheet (starting from N2 to the last row).
Avatar of Norie
Norie

Lennon

What is the purpose of the formula?
At the first glance it looks like the formlua should be

=IF(OR(COUNTIF(car$A:$A,color!A2), COUNTIF(car$C:$C,color!A2)),”Yes”,”No”)

Open in new window

or

=IF(COUNTIFS(car$A:$A,color!A2,car$C:$C,color!A2),”Yes”,”No”)

Open in new window

?
I was thinking this,

=IF(OR(COUNTIF(cars!$A:$A,color!A2),COUNTIF(cars!$C:$C,color!A2)),"Yes","No")

Open in new window


though knowing the purpose of the formula would help to convert it to VBA.
Avatar of Lennon Gary

ASKER

the purpose is to see IF lookup value (A2) is present in the column A OR column C in cars. if so return “Yes” if NOT return “No”.

I need the assistance in convert it to macro. the macro needs to check the lookup value to the last row.
Thanks, give this a try.
Function FindColor(strColor As String, ParamArray rngs() As Variant) As Boolean
Dim rng As Variant
Dim Res As Variant

    For Each rng In rngs
        Res = Application.Match(strColor, rng, 0)
        If Not IsError(Res) Then
            FindColor = True
            Exit Function
        End If
    Next rng

End Function

Open in new window


You can use it by passing the color to look for as the first argument with the ranges you want to search as further arguments.

For example to replicate the original formula you would use this.

=FindColor(color!A2, cars!A:A, cars!C:C)
thank you for your prompt response but I want to eliminate the need to type the formula. my end goal is to assign the macro to a button.
Where are the colors you want to check and where should the results go.

If the colors are in column A on 'Colors' and you want the results in column B on that sheet try this
If it's in column B on sheet 'Colors' try this.
Option Explicit

Sub CheckColors()
Dim arrColors As Variant
Dim idxRow As Long

    With Sheets("Color")
        arrColors = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value

        For idxRow = LBound(arrColors, 1) To UBound(arrColors)
            If FindColor(arrColors(idxRow, 1), Sheets("Cars").Columns(1), Sheets("Cars").Columns(3)) Then
                arrColors(idxRow, 2) = "Yes"
            Else
                arrColors(idxRow, 2) = "No"
            End If

        Next idxRow

        .Range("A2").Resize(UBound(arrColors, 1), 2).Value = arrColors

    End With

End Sub

Function FindColor(strColor As Variant, ParamArray rngs() As Variant) As Boolean
Dim rng As Variant
Dim Res As Variant

    For Each rng In rngs
        Res = Application.Match(strColor, rng, 0)
        If Not IsError(Res) Then
            FindColor = True
            Exit Function
        End If
    Next rng

End Function

Open in new window

Can I ask why you need it as VBA? Once the formula is entered it will just recalculate each time the data changes.  If the data lists are converted to tables rather than standard range/list then the formulas that refer to them will adjust accordingly and if the formula is in the table it will be copied down to new rows as new data is entered.

As always, a sample file would be useful.
I will be sending out this to the general public and don’t want the formula to be present.
In which case would it not be better to have a distribution macro that saves a copy of the file and converts everything to values.
please see the attached file for example.
example1.xlsx
See attached amended file, I have converted the two sets of data into tables and the formula in column D on the colour sheet is in table format.

If you add extra data to either of the tables then the formula will adjust to accommodate the extra data, no need for whole column references.

If you don't want the formula visible you could just protect the sheet and have the formula hidden from view, see sheet "Color (2)", I have marked the cells in column D as hidden (Format Cells > Protection > Hidden) and then protected the sheet (Review > Protect Sheet), you can add a password if you want to be more secure.
example1.xlsx
The code I posted won't leave any formulas.
@Norie where is it pasting the results though. I don’t think I can locate that in your coding.
Even with the values populated by VBA, I would assume that you would still want another code for creating a distribution copy of the file that does not include any VBA.
This put's the result on to the sheet.
      .Range("A2").Resize(UBound(arrColors, 1), 2).Value = arrColors

Open in new window

I’m perfecting fine with the VBA included in the copy I send out to the public. I can passw protect the VBA. I just want this part of coding and I can assign it to a button.
@Norie then where is your lookup value if it’s pasting it in the same cell(s).

let’s just make an example.. the lookup value is in column A starting at A2.. I want the results to be in B2 to the last row.. how will your coding be changed?
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Norie, okay testing code now.
@Norie it works perfectly! THANK YOU!

but what if I need to change the destination of the results from B to column F? how can I change that in the coding?