macro for change colum value with link other sheet

[santosh226001]

hello

We came here to solve our problem with a macro..
We want to rate revised time to time for existing data.. so we need a macro..


we have a workbook where 3 sheet's
1. Rate Sheet
2. prarup-1 (Where data in details form)
3. prarup-2 (Where data in summery form)

We want a macro to solve my following problem
1. Sheet prarup-1 from Rate sheet Rate apply with following condition

First Condition (for Colum K)

if In Colum D has any Value then Colum k (Repair Cost) we want to formula = round(colum J*Ratesheet Cell A3 ,2) .......This apply in whole row of sheet


2nd Codntion is also conditional (For Colum L)

if In Colum D has any Value then Colum L (Renewal Cost) we want formula with Folowing Condtion
if Colum E (Surface) = SDBC then use Formula round(colum J*Ratesheet Cell B3 ,2)
if Colum E (Surface) = PC then use Formula round(colum J*Ratesheet Cell C3 ,2)
if Colum E (Surface) = BC then use Formula round(colum J*Ratesheet Cell D3 ,2)
if Colum E (Surface) = p2 then use Formula round(colum J*Ratesheet Cell E3 ,2)


For Colum M
here we want formula (Colum K * Colum L Value 1% in rounded

For Colum N
here we want Sum Colum k + Colum L & Colum M



after done this we want to match Prarup 2 sheet

Total of Each Road from Colum I to Colum N We copy in Matching Road name in prarup 2 sheet From Colum H to Colum M (We mark yellow in attach Sheet)


we hope this is possbile with VBA

Thank

[xladept]

Hi Santosh,

Try this:

Please Login or Register  to view this content.

[santosh226001]

Hello Sir ur provided code is working as per my requirement... in Prarup-1 but in Prarup-2 currently data copy right in given sample sheet...

but in my original sheet..data (Road Name) in not same order as prarup-1....
for example... if in Prarup-2 Road 2 is in 2nd No. then not necessary...road 2 in also in 2nd place in Prarup-2... may bhi 5th Place.. 8th Place.. or any other place.. so i think when we copy data in Prarup-2 then a match condtion must be in road name so that.. we copy TOTAL LINE DATA IN right place

one more thing in following line you define code as all road name must be Road .....but it's only in sample file not in original data so in original data not transfer in prarup-2

Please Login or Register  to view this content.

my orginal data road name in following type

Karanpur Link Road
Lucknow-Ballia via Azamgarh State Highway
Rajepura Samgra City Road
Etc...


THANKS.. FOR HELP & WE ALSO ADD REPUTATION FOR THIS VALUABLE HELP..
sANTOSH

[xladept]

I'll spend some time on it tomorrow - are there any more differences? Should you supply a different sample??

[santosh226001]

No problem sir.. u take your time.. as per ur request we attach update sample file with Different Different road name & Also Different Sequence order

[xladept]

Hi Santosh,

See if this works for you:

Please Login or Register  to view this content.

[santosh226001]

Yes sir it's works as per my requirement.. thanks a lot..

We add reputation

Thank you so much..for providing above macro

[xladept]

You're welcome! Thanks for the rep

[santosh226001]

sorry sir once again we come here.. today when we work on my database then we want a littl bit more in above code which is..following

after update rate
In prarup-1 sheet if Colum P (If Most Priority ) ="YES" then Individual Road Colum N Value Sum Display in Prarup-2 colum O

for example in above sheet First Road Colum P Yes Value is 127.26 (6 time yes come in First row)

Thanks

[xladept]

Hi Santosh,

Try this:

Please Login or Register  to view this content.

[santosh226001]

Hello Sir.. we apply ur updated code in my sheet but.. result not come actually we want..

SIR WE WANT SUM ONLY THOSE VALUE OF COLUMN N.... IF COLUM P VALUE IS "YES"... if colum P value is <blank> then Colum n Value NOT Sum....for examp

if Cell Value P6=YES ... AND TOTAL COST VALUE CELL N6 WILL BE SUM ( THINK N6 VALUE 22.00)
if Cell Value P7=YES ... AND TOTAL COST VALUE CELL N7 WILL BE SUM ( THINK N7 VALUE 22.00)
if Cell Value P8=<BLANK>... AND TOTAL COST VALUE CELL N8 WILL NOT SUM (vALUE NOT SUM)
if Cell Value P9=YES ... AND TOTAL COST VALUE CELL N9 WILL BE SUM ( THINK N9 VALUE 22.00)

RESULT WILL BE COPY IN COLUM O OF PRARUP-2 SHEET (22+22+22) = 66

CURRENTLY ALL VALUE OF COLUM N IS SUMMING ....& IN PRARUP-2 SHEET ONLY FIRST 4 ROW DATA DISPLAY.. NEXT 2 ROW RESULT NOT DISPLY..

tHANKS

[xladept]

Hi Santosh,

I'll try again tomorrow.

[xladept]

Hi Santosh,

Try this:

Please Login or Register  to view this content.

[santosh226001]

Yes.. this is what i want....Thanks a lot....

[xladept]

You're welcome!