Help with VBA...better way to do this simple task, please.

mprusak

New Member
Joined
May 5, 2003
Messages
36
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,263
Office Version
  1. 365
Platform
  1. Windows
Mark

Try this.
Code:
    For I = 1 To 60
        Worksheets("Parts2").Range("D80").Value = Worksheets("Parts2").Range("D15").Offset(I - 1).Value
        Worksheets("Parts2").Range("E80").Value = Worksheets("Parts2").Range("E15").Offset(I - 1).Value
        Worksheets("Parts2").Range("J15:AF15").Offset(I - 1).Value = Worksheets("Parts2").Range("C4:Y4").Value
    Next I
 

mprusak

New Member
Joined
May 5, 2003
Messages
36
Thanks for the quick reply.

That seems to work. It reduces the amount of code, which is nice, but doesnt help it run any faster. Its doing the same routine, just coded more efficiently. This is not a bad thing.

However, I think if it had the IF functionality in there to ignore empty groups, that it would run much faster. Any thoughts on how to do this?

Basically, IF D15 is blank then next I, if D16 is blank then next I, etc.

Thanks again! Great start.
 

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
Hi,

I'm not completely clear on what the condition is - is it blank for both miles AND months or a blank for either miles OR months? For the latter, perhaps try something like:

Code:
Sub example()

Dim i   As Long
Dim rng As Range


With Worksheets("Parts2")
    For i = 0 To 59 ' loop through the 60 groups
        Set rng = .Range("D15:E15").Offset(i, 0)
        If Application.Count(rng) = 2 Then ' test for empty miles or months
            .Range("D80:E80").Value = rng.Value
            .Range("J15:AF15").Offset(i, 0).Value = .Range("C4:Y4").Value
        End If
    Next i
End With


End Sub
For an introduction on using looping structures try the following:

 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,263
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try this.
Code:
   For I = 1 To 60
        If Worksheets("Parts2").Range("D15").Offset(I - 1).Value <> "" Then

           Worksheets("Parts2").Range("D80").Value = Worksheets("Parts2").Range("D15").Offset(I - 1).Value
           Worksheets("Parts2").Range("E80").Value = Worksheets("Parts2").Range("E15").Offset(I - 1).Value
           Worksheets("Parts2").Range("J15:AF15").Offset(I - 1).Value = Worksheets("Parts2").Range("C4:Y4").Value
        End If
    Next I
 

Watch MrExcel Video

Forum statistics

Threads
1,127,115
Messages
5,622,813
Members
415,934
Latest member
adstocking

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top