I am a very basic VBA user. I have this working, but am looking to improve on it. There is probably a much more efficient way to do this basic process.
I have a dynamic table that returns cost information based on entering two pieces of information (months and miles). I want to run up to 60 sets of numbers and return the results for each scenario.
Example:
Group 1: Enter 72 months and 400,000 miles - get resulting Group 1 data
Group 2: Enter 60 months and 300,000 miles - get resulting Group 2 data
etc...do this 60 times...
I currently have the following code:
<code>
'Group 1
Worksheets("Parts2").Range("D80").Value = Worksheets("Parts2").Range("D15").Value
Worksheets("Parts2").Range("E80").Value = Worksheets("Parts2").Range("E15").Value
Worksheets("Parts2").Range("J15:AF15").Value = Worksheets("Parts2").Range("C4:Y4").Value
'Group 2
Worksheets("Parts2").Range("D80").Value = Worksheets("Parts2").Range("D16").Value
Worksheets("Parts2").Range("E80").Value = Worksheets("Parts2").Range("E16").Value
Worksheets("Parts2").Range("J16:AF16").Value = Worksheets("Parts2").Range("C4:Y4").Value
</code>
(...repeating 60 times)
So, the first section takes the months for Group 1 (D15) and the Miles for Group 1 (E15) and enters into the corresponding table fields (D80 and E80). The results that are provided for these sets of numbers kick out in C4 to Y4 and I then copy the values to the Group 1 Row (J15 to AF15).
Next section, repeats the process for the next Group, located on the next Row (16)
This process is repeated for all 60 groups (with 60 sections of code like the above).
Two things I would like to do better to see if it can run a little faster (it currently takes about 10 seconds) and have it skip Groups that are empty.
1 - Can the code be written more efficiently using a loop or something?
2 - in most cases, the majority of these 60 groups are empty (maybe 1-5 need to be processed and anything showing no miles or months information, can be ignored). How do I structure this IF scenario to ignore the blank Groups?
Thanks very much for taking to provide any advice to get me pointed in right direction.
Mark
I have a dynamic table that returns cost information based on entering two pieces of information (months and miles). I want to run up to 60 sets of numbers and return the results for each scenario.
Example:
Group 1: Enter 72 months and 400,000 miles - get resulting Group 1 data
Group 2: Enter 60 months and 300,000 miles - get resulting Group 2 data
etc...do this 60 times...
I currently have the following code:
<code>
'Group 1
Worksheets("Parts2").Range("D80").Value = Worksheets("Parts2").Range("D15").Value
Worksheets("Parts2").Range("E80").Value = Worksheets("Parts2").Range("E15").Value
Worksheets("Parts2").Range("J15:AF15").Value = Worksheets("Parts2").Range("C4:Y4").Value
'Group 2
Worksheets("Parts2").Range("D80").Value = Worksheets("Parts2").Range("D16").Value
Worksheets("Parts2").Range("E80").Value = Worksheets("Parts2").Range("E16").Value
Worksheets("Parts2").Range("J16:AF16").Value = Worksheets("Parts2").Range("C4:Y4").Value
</code>
(...repeating 60 times)
So, the first section takes the months for Group 1 (D15) and the Miles for Group 1 (E15) and enters into the corresponding table fields (D80 and E80). The results that are provided for these sets of numbers kick out in C4 to Y4 and I then copy the values to the Group 1 Row (J15 to AF15).
Next section, repeats the process for the next Group, located on the next Row (16)
This process is repeated for all 60 groups (with 60 sections of code like the above).
Two things I would like to do better to see if it can run a little faster (it currently takes about 10 seconds) and have it skip Groups that are empty.
1 - Can the code be written more efficiently using a loop or something?
2 - in most cases, the majority of these 60 groups are empty (maybe 1-5 need to be processed and anything showing no miles or months information, can be ignored). How do I structure this IF scenario to ignore the blank Groups?
Thanks very much for taking to provide any advice to get me pointed in right direction.
Mark