Trying to use excel formula or VBA

1
votes

I have two different workbooks. Book 1 and Book 2 both have the same number of columns A through M.

I want to do match the records between two workbook, for example: I have a column A name Birthday, Column B City, Column C Passport Number......., in worksheet 1 & 2.

I want to match worksheet 1 Cell A1 from the Range A:A worksheet 2, If the record in column A cell 13 not matching it shows Birthdate not match in N13 Workbook 2, If it does not match with worksheet 1 Cell 13 from the Range B:B worksheet 2 it shows city not MATCH in Column N 13 in workbook 2, and so on till column M.

I am using the formula below but it's not working properly, I don't know what I am missing and what formula should I add in. I have no idea about VBA. But I want to see is it easier to do by using excel formula or vba? 

IF(COUNTIF(Target!$A$2:$A$5964,Source!A8)=0,"Birthday",IF(COUNTIF(Target!$B$2:$B$5964,Source!B8)=0,"City",IF(COUNTIF(Target!$C$2:$C$5964,Source!C8)=0,"Country",IF(COUNTIF(Target!$E$2:$E$5964,Source!D8)=0,"Passport Number Mismatch in Target",IF(COUNTIF(Target!$F$2:$F$5964,Source!E8)=0," Travel Date Mismatch in Target",IF(COUNTIF(Target!$G$2:$G$5964,Source!F8)=0,"First Name Mistmatch in Target",IF(COUNTIF(Target!$H$2:$H$5964,Source!G8)=0,"Full Name Mismatch in Target","Match in Target")))))))

Thanks in Advance.

1
vbaexcelexcel-formulaexcel-2010

1 Answers

1
votes

VBA has access to these same worksheet formula functions (e.g. COUNTIF): there really aren't column or matrix functions that VBA has that formulas don't have.

However, VBA lets you write loops (e.g. while, for), it allows if-statements, procedure calls, and many lines of code so your calculations can have more steps and hence be more complex. VBA also lets you have temporary space in the form of arrays (and strings and objects, too) (so you don't necessarily need to use columns for temporary space as one might do with formulas). VBA also allows recursion, which makes some calculations easier (to some definition).

VBA provides an imperative programming model. VBA procedures can read and write any cell of the spreadsheet. Imperative programming, on the other hand, needs to be triggered somehow such as by using a button.

By contrast, the data-flow programming model with formulas will automatically recalculate whenever their input sources change, which is good. But there are some cases it doesn't handle naturally (e.g. recursion).

Another option is to combine VBA with formulas, by writing new formulas that are then implemented in VBA. If you are doing that, the VBA can only return information thru function return values; it cannot otherwise modify the spreadsheet.

So, if you can think of how to do this easier using loops (and arrays) or recursion and maybe with a button to trigger the computation (or by using custom formulas) then VBA might be interesting.