Filling values from the column on left or right

[VKS]

Hello Friends,
This novice user of VBA is back with another problem. Being a fairly good user of excel I work on huge pivot table called Master File created from a very huge access table.
I use this pivot table to cut paste info for various reports. Every time I cut and paste something my table looks like the sample shown below.
I find spending a lot of time in writing a formula in excel which is =IF(A2=””,B1,A2) then I copy and paste it till the last line of data (sometimes in thousands of rows) this works fine for me but since I use this more often so I decided to write a small macro for myself as shown below

Please Login or Register  to view this content.

And this didn’t fill even a single cell for me forget about going down 2500 rows.
I wonder if some expert can correct my formula above and guide me to understand coz after this I would want to write a code for picking up values from right column and do it till end.
Regards/VKS
Months Sales Person Item Sales area A Sales area B Sales area C
Jan Jack a 1 5 2
Jill a 2 3 1
Bill a 0(0 is Blank in data) 9 6
Tom a 5 1 1
Sam a 1 2 1
Feb Jack a 1 5 2
Jill a 2 3 1
Bill a 0(Blank in data) 9 6
Tom a 5 1 1
Sam a 1 2 1

[watersev]

hi VKS, if I understood your request correctly, try this option:

Please Login or Register  to view this content.

[VKS]

Thanks for your help i will just check this out and let u know the result.

[VKS]

U are a star (already clicked the one below your post)....
Can you please also tell me what each row means so that i can build a code for picking up data from right column

Hope not expecting too much (I really want to learn VBA) my excel clubbed with VBA should make me a better professional.

[watersev]

Please Login or Register  to view this content.

- find last row cantaining any data in C column

Please Login or Register  to view this content.

- if last row equals 1 then exit sub

Please Login or Register  to view this content.

- paste Excel formula =if(A2<>"",A2,B1) through VB for range B2:B & lrow

[VKS]

Thanks a lot Watersev...... you have been a great help in my todays learning
Marking it as solved but i have coming up in my mind.... what happens if you have to fill column K based on data in column J and rest everything remains same (i mean the formula to be used etc.)
Thanks again.....

[watersev]

Please Login or Register  to view this content.

[VKS]

So my guess was right. We will have to change the code very time if we want to fill from a different column.
I mean we cant tell our code to pick up values from adjecent left coulmn of the active cell..... hmmmm
Thanks again

[watersev]

you have not asked for that ...

[watersev]

Please Login or Register  to view this content.

you need to decide how you will define range for a formula: Range("b2:b" & lrow)

[VKS]

I am so sorry Watersev. As i learn more from you... more questions keep coming up.
Every solution you provide i put it into real life situation and that gives me another situation.....
I think i am being a pain here now..... but still....you have made my day.