Looping

[Nordeen1]

I just started trying to learn VBA this week so I am very inexperienced. My situation is as follows:

I have a column of data on Worksheet 1.
I want the first cell in that list to be copied into a specific cell on Worksheet 3.
Once the cell on Worksheet 3 is populated there will be calculations carried out based upon the value of the copied cell.
I then want those calculations to be copied back to Worksheet 1 right next its corresponding value.
I then want the VBA to do the same thing for the rest of the cells in the column.
The end result should be that with the click of a button the code will put all of the calculations next the the corresponding original values on Worksheet 1.

I am fine up until when I need the code to proceed onto the next values. Do I need a loop to accomplish my goal?

I have attached my file so that it can be viewed.

My codes are as follows:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks!

[gjcase]

I would suggest you copy the data from sheet 1 into an array, then process the array, then paste the array back into the adjacent column on sheet 1. No need to use sheet 2 at all and much quicker if you have a large dataset.

---GJCase

[gjcase]

Looking at your spreadsheet, it looks to me like you'd be better off with a table of values vs complicated formulae on the second sheet, especially if the data will change periodically.

Then you just need a lookup on the first sheet against the table of data on the second one.

---GJCase

[protonLeah]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.


Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[Nordeen1]

gjcase,

Thank you for your responses.

I understand that there are probably better ways to accomplish what I want and that if I attached my original file you would be able to better help me but I cannot paste my original file due to sensitive information and it is too difficult to accurately re-create a workbook that illustrates my problem but leaves out the sensitive information. I understand what I’m trying to do may be crude but for the sake of getting this project completed I want to proceed in this fashion and I will hopefully refine the workbook and code in the future. That being said I would like to try another angle. Since I did not do a good job clearly explaining my problem in my first attempt please disregard that first scenario and consider the following:

I have a list of Titles on Sheet 1 in Column 1.
I want to copy the first Title in Column 1 on Sheet 1 and paste it on Sheet 3 in Cell A1.
Once the Title is pasted in Cell A1 on Sheet 3, formulas will be calculated in Cells B1:D1 on Sheet 3.
I then want to copy Cells B1:D1 on Sheet 3 and paste the values on Sheet 1 next the respective Title.
After that is completed I want the macro to carry onto the rest of the Titles in Column 1 on Sheet 1 until every Title has its respective outputs from the calculations next to it.

Thank you very much!