VB for making the changes in report to retrieve the correct values

[laansesu]

Dear All,

We used to get a report from another department which is very important for our monthly production

But there are few things that needs to be changed in that report for retrieving those values.

If I miss any of the sub-tasks mentioned below, It is going to be big messup

I've recorded a macro for doing the below tasks with a single click rather than doing it manually. But here problem is recording macro doesn't resolve my issue and there are few things that needs to be executed by editing this code where as I'm unaware of VB at all. So request someone to go through the below tasks & help me in the code

Attached excel with Sheets SSR (this is the actual report which we get) & SSR_After (this is the report after changing it to retrieve the exact values)

1. Unhide all in SSR Sheet
2. Unmerge 5th Row and write formula =B5 in C5 and paste special this formula in the right side blank cells
3. Lookup location of SSR to Sheet1 (=VLOOKUP(B5,Sheet1!$A:$A,1,0)) at Row 4 and delete columns where this formula returns NA Error
3. Insert column at Column B, Lookup Models of SSR to Sheet2 at B Column (formula at B8 =VLOOKUP(A8,Sheet2!B:D,3,0) and drag down till end)
3. Delete the rows where above formula returns NA Error
3. Make all the values as zero (from C8 to last column for all the models) if above formula returns "r" except if Row 4 has "Secunderabad" & "Vijaywada"
5. Insert 7th Row and write formula =B5&B6
6. Lookup location of ssr to Sheet1 at 4th Row & Delete columns where there is #N/A
7. Make E Column of Sheet3 as zero where there is no formula
8. Make Range from B51 to C53 in Sheet3 as zero

[laansesu]

Dear All,

Could Someone reply on this.

[laansesu]

Dear All,

Could someone reply on this.

[LeoTaxi]

this one is far from perfect

just give it because there are no other suggestions so far



Kind regards
Leo

[laansesu]

Thanks & Perfect Leo!!!

One and only small change & Help required is, whereever column has BAL then I need formula to be shown =SSR Value - DESP Value

SSR Value & DESP Value - Value where it is in SSR Column & DESP Column respectively

ex: for Delhi, I8 (DELHI BAL VALUE) should not be static value but formula should be =F8-G8 where F8 is DELHI SSR Value & DELHI DESP VAlue

Please do this small favor and help me.

Regards
Anil T

[LeoTaxi]

If between SSR en BAL are always 2 columns this can do


Kind regards
Leo

[laansesu]

Thanks a Ton Leo!!!!

I want to learn VB from you & expertize my knowledge!!!

[LeoTaxi]

could it be last column BAL is not done ?

if so it is solved in this


Kind regards
Leo

[laansesu]

Hi Leo,

Thanks for your previous help. we have got another report from the concern dept and one point seems to be not executing with VB i.e., as per the above points mentioned in the above requirement, below point is making zero for all values that formula return "r" whereas I dont want to make "Secunderabad" & "Vijayawada" as zero except this I want to make all of the others as zero.

3. Make all the values as zero (from C8 to last column for all the models) if above formula returns "r" except if Row 4 has "Secunderabad" & "Vijaywada"

[LeoTaxi]

Line with a change in red

Please Login or Register  to view this content.

Kind regards
Leo

[laansesu]

Dear Leo,

With the above code, Everything should be zero except secunderabad & Vijayawada. But these are not becoming zero.

Please do the needful.

[LeoTaxi]

3. Make all the values as zero (from C8 to last column for all the models) if above formula returns "r" except if Row 4 has "Secunderabad" & "Vijaywada"

changed to

Make all the values as zero (from C8 to last column for all the models) except if Row 4 has "Secunderabad" & "Vijaywada"

Kind regards
Leo

[LeoTaxi]

| and ?

[laansesu]

Dear Leo,

As per your attached file, there is no code for SSR Sheet in order to make the above said changes

Can you please check and provide the revised file.

[LeoTaxi]

see, wrong attach


kind regards
Leo

[laansesu]

changed to

Make all the values as zero (from C8 to last column for all the models) except if Row 4 has "Secunderabad" & "Vijaywada"

Kind regards
Leo

3. Insert column at Column B, Lookup Models of SSR to Sheet2 at B Column (formula at B8 =VLOOKUP(A8,Sheet2!B:D,3,0) and drag down till end)
3. Delete the rows where above formula returns NA Error
3. Make all the values as zero (from C8 to last column for all the models) if above formula returns "r" except if Row 4 has "Secunderabad" & "Vijaywada"

As per the above 3 lines, where 2nd point is being excuted well (Delete the rows where above formula returns NA Error) where as third point I dont want to make everything as zero. I want to make C8 to till end as zero if the above formula return "r" & if 4th row not equals to Secunderabad" & "Vijayawada"

[LeoTaxi]

like this ?

Kind regards
Leo

[laansesu]

like this ?

Kind regards
Leo

You are very close to my requirement, as of now we are keeping the values as is with this VB Code for Secunderabad & Vijayawada and making everything as zero. I want to be zero for the below Models only, not for the entire list

Please go through the below list in sheet2 also where I've marked in the Sheet-2 D Column as "r"

Model wise as per client
CB1200MM-OPW-WOR
CB1200MM IVY- WOR
CB1200MM LUSTERBRN- WOR
CB1400MM OPW-WOR
CB1400MM IVY-WOR
CB1400MM LUSTER BRN-WOR
WWDECORA1200-OPW-WOR
WWDECORA1200-IVY-WOR
WWDECORA1200-LB-WOR
WWHE1200-OPW-WOR
WWHE1200-IVY WOR
WWHE1200-L/BRN WOR
WWHE1400-OPW-WOR
WWHE 1400 IVY WOR
WWHE1400-L/BRN WOR

[LeoTaxi]

can you tel me the list above is a other list then the list you get with the "r" in colymn B ?

[laansesu]

can you tel me the list above is a other list then the list you get with the "r" in colymn B ?

Actually Leo, I didn't get you. I'm sorry if I have confused you. But can you please clarify your comment so that I can give you a clarrity

[LeoTaxi]

same for me Laansesu

i dont see a difference
maybe you show the difference in excel file


Kind regards
Leo

[laansesu]

Dear Leo,

Sheet1 has the details as per our format & SSR Sheet has the details as per client format.

So I'm using Sheet2 as helper column/sheet to map Sheet1 & SSR.

whereever these models marked as "a" we are serving those models in all locations & whereever models marked as "r" we are serving only 2 locations secunderabad & Vijayawada.

Is this clarrification sounds good for you.

[LeoTaxi]

no dont get it
maybe like your exemple before with a SSRsheet after
give a new sheet after, think only then i can go on


Kind regards
Leo