Lennon Gary
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,co lor!A2,COU NTIF(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).
I need to rewrite the following excel formula into VBA.
=IF(OR(COUNTIF(car$A:$A,co
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).
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”)
or=IF(COUNTIFS(car$A:$A,color!A2,car$C:$C,color!A2),”Yes”,”No”)
?
I was thinking this,
though knowing the purpose of the formula would help to convert it to VBA.
=IF(OR(COUNTIF(cars!$A:$A,color!A2),COUNTIF(cars!$C:$C,color!A2)),"Yes","No")
though knowing the purpose of the formula would help to convert it to VBA.
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.
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.
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)
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
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)
ASKER
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.
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
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.
As always, a sample file would be useful.
ASKER
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.
ASKER
please see the attached file for example.
example1.xlsx
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
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.
ASKER
@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
ASKER
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.
ASKER
@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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Norie, okay testing code now.
ASKER
@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?
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?
What is the purpose of the formula?