Mathematical operations with variables using loops

[sandeep830808]

Hello,
i have tried to write a VBA code for Mathematical operations on columns with a variable using loops. i am unable to figure out how to rectify the code.
my work involves forecasting of monthly production. i have the monthly production data from
March-June. if there is a small increase the forecast for June say x % ( x is a variable ). i should be able to produce data in this manner.
Forecast for June = Column June + (x*Column June )/100.
I have written the code for this .but i am having some serious errors.
any help will be appreciated

Please Login or Register  to view this content.

[nimrod]

So are you wanting to overwrite the old values , in a month column, with the new values ?.. ie. loop thru every number in a column and update that number by using you caclulation ? Or.... are you wanting to view a total for column based on change ? In other word what are you expecting for results ?

[nimrod]

This code will update the numbers in a specified column . By-The-Way .. excel has a built in feature for doing just that i.e. the Muliply feature on the "PasteSpecial" window is for this very type of thing.

Please Login or Register  to view this content.

[sandeep830808]

Hello Nimrod,
thank you for your help. I have another question . what if i wanted to paste the results in another column instead of overwriting on the same column. Any suggestions ?

[nimrod]

Could you be more specific ? There are many ways this could be done , and each way would be different codine ... for exampe
-- at run time you tell macro month and indicate where to paste results
-- The macro always paste to the same predetermined column
-- The macro inserts a column beside the old data ( though this would mean changing how you prompt for the months column since they'd be dynamic

[sandeep830808]

Hello Nimrod,
Thanks for your help. i want the Forecast Data for March Displayed in column R, April in Column S...and so on. By doing this i can actually see the original data in columns L to Q, and the forecast change from Columns R to W. Any suggestions about how to do this.

[snb]

Put the month you want to forecast in A1 (jan=1, feb=2, etc)
Put the percentage you want to use in the forecast in A2 (1,30,90,120,250, etc)
This macro stores the result in column AA, AB, AC, etc

Please Login or Register  to view this content.

[nimrod]

To see changes just follow "ResultColumnOffset "

Please Login or Register  to view this content.

[sandeep830808]

Hello Nimrod,
Thanks for the help. the code works but there is a little mix up.
The source columns are also getting updated along with the target columns .
The code copies the source column, and paste it in the Target column. then updates both of them.
I source column should not get updated, only the target column should be updated. I tried various changes but none seems to work. Can you suggest some change.

[nimrod]

How's this ?

Please Login or Register  to view this content.

[sandeep830808]

hello Nimrod,
Thanks once again.The code works only for the Month of March i.e 3, and doesn't seem to work for the others. I am clueless to what's wrong in the code.

[nimrod]

OOPPSS sorry "MonthNum - FirstMonthNum" was inverted and it was moving to the left , instead of to right , for each month added.. sorry

Please Login or Register  to view this content.

[sandeep830808]

Hello Nimrod,
Thanks once again, Now the code works perfectly. My VBA skills are not that advanced. This helped my learning too.

[nimrod]

My pleasure ... please let me know if you'd like any lines explained ... to aid in your education of VBA.