• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to replace Nested IF & VLOOKUP

A

ashish mehra

Active Member
Hi all,

I working on a project that contains extensive usage of IF & Vlookup formulas. I am looking for a vba based solution to replace the formulas.

Working:

On the basis of the Product & Source columns; I have setup formulas which look something like this:

1) Start Date
=IF(AND(J3="NCR",O3="PWD"),VLOOKUP(M3,'[Dummy Source.xlsb]NCR_PWD'!$B:$FK,42,0),IF(AND(J3="LOP",O3="PWD"),VLOOKUP(M3,'[Dummy Source.xlsb]LOP_PWD'!$B:$GI,40,0),IF(AND(J3="NPSC",O3="PWD"),VLOOKUP(M3,'[Dummy Source.xlsb]NPSC_PWD'!$B:$GK,47,0),"Check Formula")))
Click to expand...

2) Amount
=IF(AND(J3="NCR",O3="PWD"),VLOOKUP(M3,'[Dummy Source.xlsb]NCR_PWD'!$B:$FK,162,0),IF(AND(J3="LOP",O3="PWD"),VLOOKUP(M3,'[Dummy Source.xlsb]LOP_PWD'!$B:$GI,186,0),IF(AND(J3="NPSC",O3="PWD"),VLOOKUP(M3,'[Dummy Source.xlsb]NPSC_PWD'!$B:$GK,188,0),"Check Formula")))
Click to expand...

3) Status
=IF(AND(J3="NCR",O3="PWD"),VLOOKUP(M3,'[Dummy Source.xlsb]NCR_PWD'!$B:$FK,19,0),IF(AND(J3="LOP",O3="PWD"),VLOOKUP(M3,'[Dummy Source.xlsb]LOP_PWD'!$B:$GI,17,0),IF(AND(J3="NPSC",O3="PWD"),VLOOKUP(M3,'[Dummy Source.xlsb]NPSC_PWD'!$B:$GK,20,0),"Check Formula")))
Click to expand...

This is one combination of Product & Source columns; there are 8 more combinations.

The list of columns continues till 30; i.e. for every column there is a separate formula need to be evaluated.

Regards,
AM:)
 
@ashish mehra for crosschecking ...
1) open and save 'Output.xlsb' -file to same folder with 'Dummy Source.xlsx'
2) DO NOT open 'Dummy Source.xlsx' !
3) Press [ Fill Data ]
4) Red cell tells missing informations...
 

Attachments

  • Output.xlsb
    20.8 KB · Views: 4
@vletm

I am receiving "Cannot open Dummy Source.xlsx -file!" msgbox. Please check.

I did not open Dummy Source.xlsx as per instruction.

Regards,
AM:)
 
@ashish mehra
Are both files in same folder?
That message tells,
that it have not found 'Dummy Source.xlsx'-file from same folder.
Open only 'output.xlsb'-file.
 
@vletm

Yes both files are in same folder.

1) I comment out "Workbooks.Open (WB_2)"
2) Manually open the file

The code is working fine.;)

I am implementing your macro in working file & will ping you when require.:cool:

Thanks a lot!

Regards,
AM:)
 
:)@vletm

I need further help.

I have added new sheet in Dummy Source file with new criteria.

Can you please check & include the same in existing code.

Regards,
AM
 

Attachments

  • Dummy Source.xlsx
    13.7 KB · Views: 2
  • Output.xlsb
    19.2 KB · Views: 1
@ashish mehra
1) new: have to select 'Dummy Source.xlsx' or any file ...
2) I think that there were LPS's SPU & SKU in wrong columns?
Compare with Your files...
 

Attachments

  • Output.xlsb
    25.3 KB · Views: 2
:)@vletm

1) You have to select Dummy Source file only; I have added new worksheet named LPS_DA
2) Yes you are right in pointing LPS's SPU & SKU are all placed wrong in Dummy Source file.

This time, if Source column is "DA" and Product column is "LPS" then SKU (LPS454) is the lookup value.

For your reference I have applied Vlookup formula. If you remove space before formula then you will easily understand the requirement.

Edit: Yes you are right in pointing LPS's SPU & SKU are all placed wrong in Dummy Source file.

Updated file is attached.

Regards,
AM
 

Attachments

  • Dummy Source Updated.xlsx
    13.7 KB · Views: 0
Last edited:
@ashish mehra
please, next time tell changes,
not just 'I have added new sheet in Dummy Source file with new criteria.'
Some formulas can have many characters to check.
Before 'LPS' all 'Products' used same rules, now there are one minor difference that You knew.
test this...
 

Attachments

  • Output.xlsb
    25.6 KB · Views: 2
@ashish mehra
Is it windy there?
Here were -28C in this morning, clear sky.
Tomorrow will be colder!

I just made changes ... before Your #11 Reply (Edit #10).
You could tell 10-15 minutes later, what is right or wrong, could You?
I will try to do some of my normal things now!
 
vletm said:
@ashish mehra
Is it windy there?
Here were -28C in this morning, clear sky.
Tomorrow will be colder!

I just made changes ... before Your #11 Reply (Edit #10).
You could tell 10-15 minutes later, what is right or wrong, could You?
I will try to do some of my normal things now!
Click to expand...
@vletm

Sorry for the confusion. I can understand your pain.
 
vletm said:
@ashish mehra
please, next time tell changes,
not just 'I have added new sheet in Dummy Source file with new criteria.'
Some formulas can have many characters to check.
Before 'LPS' all 'Products' used same rules, now there are one minor difference that You knew.
test this...
Click to expand...
@vletm

Many thanks for updating code.

I could not understand the use of "Dim x_col(4, 4)". Please explain.

Regards,
AM
 
@ashish mehra
This version has same routines with all 'Products' SPU.
I swap LPSs SKU and SPU ... You know those.
Dim x_col(4,4) ... have source columns for each Product.
Do this work now?
 

Attachments

  • Output.xlsb
    24.2 KB · Views: 3
@vletm

Yes the code is amended in the real environment & is working fine.

From Reply# 16; Do I need to download the attached file?

Is it different from reply# 12?

Regards,
AM
 
@ashish mehra
HUH! #16 Reply...
I wrote 'this version...', so it's the newest version (file sizes are different too).
I understood that LPS should work as all other 'Products'. #10 Reply
So, it's different than #12 Reply.
The last line was ... 'Do this work now?'
Yes, yes
... so, please test the newest version from #16 Reply
 
@vletm

I test the file in Reply# 16. My requirement suits to Reply# 12.;)

Thanks for your help.:)

I have successfully implemented the code in my office on 250 lines; it took 150 seconds. I am trying to minimize the macro time.

Regards,
AM:)
 
You must log in or register to reply here.
Back
Top