vlookup help

[Abdul1987]

Hi,

i need help with the code for vlookup

i have my data in column A and B and i want to add vlookup in column C between data of B to A , and if any value in the cell is not matching i mean if it is #N/A that line should be highlighted in yellow. please help

Regards.

 

[Sam Mathai Chacko]

Can you post a sample file and explain what exactly is the input and what's the expected output?

 

[Sajan]

Hello Abdul,
If I am understanding your question, you are wanting to check if each row's value in column B does not have a match in the entire column A, then you want that row highlighted in yellow.

If that is correct, then you can use a formula such as the following as a "Conditional Format" formula, selecting the entire range in column A and B:
=ISNA(MATCH($B1,$A$1:$A$7,0))

In the above formula, column A is assumed to be from A1:A7. Adjust it to your range.

Cheers,
Sajan.

 

[Abdul1987]

Thank you for the reply i really appreciate it.

let me reframe my question in more detail

i have a workbook with results tab ( data in column A) and other workbook with entries tab ( column A)

every day i do vlook up between these 2 workbooks to find any new entries in results sheet ( column B)

if i find #N/A i need to highlight that row and send it for processing

hence looking for macro which can do this for me every day insted of manually putting vlookup on daily basis

hope i am clear with my requirement

Please help

Regards,
Abdul

 

[Sajan]

Hi Abdul,
Please supply a sample workbook or workbooks... I think you are saying you are wanting to lookup a value in one workbook in a second workbook, but am not sure.

Also, are you using the term "macro" to refer to formulas, or actual VBA code. If you are looking for VBA code, then someone else will need to help you.



-Sajan.

 

[Sam Mathai Chacko]

So essentially, you want a macro to automatically put in those VLOOKUP formula when you click a button?

 

[Abdul1987]

Yes Please Sam, it would be a great help

 

[Abdul1987]

Hi Abdul,
Please supply a sample workbook or workbooks... I think you are saying you are wanting to lookup a value in one workbook in a second workbook, but am not sure.

Also, are you using the term "macro" to refer to formulas, or actual VBA code. If you are looking for VBA code, then someone else will need to help you.



-Sajan.

Hey Thanks man for the effort you put in to look into my question.

 

[SirJB7]

Hi, Abdul1987!
That would be a sort of macro for writing formulas... It's not a bad idea, in fact I want something alike but that reads my mind, and if it's possible that doesn't obey me and writes the correct formulas.
Regards!

 

[Sam Mathai Chacko]

Sure. This is an example of how you can do it.

1. Click on the record macro button on the bottom left (Excel 2007 and above)
2. Give a suitable name to your macro
3. Assign some shortcut key (I've used CTRL+SHIFT+r)
4. Select Personal Macro Workbook
5. Hit OK
6. Now type something in the active cell
7. Hit ALT+F11 (this will open the VBE)
8. You will find the personal workbook.xlsb in the project explorer to the left
9. Open the module (you'll find the macro with the name you provided)
10. Replace the code (it should look something like this: ActiveCell.FormulaR1C1 = "fdafs") with what I gave
11. Now ensure the formula and the ranges I used are corrected according to your requirement.
12. Close Excel application and ensure you save the personal macro workbook. You should now be done.
13. When you wish to run the macro by hitting the shortcut key, ensure your active workbook is the one where you want the formula to be inserted

    ActiveSheet.Range("B1:B100").Formula = "=MATCH(A2,'[The File That You Want To Lookup From.xlsx]Sheet1'!$A$1:$A$50,0)"

Post back if you aren't able to make it work