Code optimization

N

neznanec

New Member
Joined
Jan 31, 2013
Messages
2
I'm working with table which looks like this [link]. Every month increases for one column and I have to manually correct formulas. I'm trying to make things easier so I start creatin a macro. As I've never write any code, I don't know how to make correct loops to fill all cells with appropriate formulas.

I did following code to fill first four coloumns with appropriate formulas:

Code: 
Sub Insert()


    Cells.Find(what:="Sales Units", After:=ActiveCell, LookIn:=xlFormulas, lookat _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    
    Cells.Find(what:="CP/R1", After:=ActiveCell, LookIn:=xlFormulas, lookat _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    
    Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
        
        Selection.Offset(1, 1).Select
        ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-2])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-2])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-2])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-2])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-2])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-2])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-2])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-2])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-2])*100"
        Selection.Offset(4, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-2])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-2])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-2])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-2])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-2])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-2])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-2])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-2])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-2])*100"
        
        Selection.Offset(-20, 1).Select
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-14])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-14])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-14])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-14])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-14])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-14])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-14])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-14])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-14])*100"
        Selection.Offset(4, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-14])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-14])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-14])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-14])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-14])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-14])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-14])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-14])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-14])*100"
        
        Selection.Offset(-20, 1).Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])/SUM(RC[-16]:RC[-15])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])/SUM(RC[-16]:RC[-15])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])/SUM(RC[-16]:RC[-15])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])/SUM(RC[-16]:RC[-15])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])/SUM(RC[-16]:RC[-15])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])/SUM(RC[-16]:RC[-15])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])/SUM(RC[-16]:RC[-15])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])/SUM(RC[-16]:RC[-15])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])/SUM(RC[-16]:RC[-15])*100"
        Selection.Offset(4, 0).Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])/SUM(RC[-16]:RC[-15])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])/SUM(RC[-16]:RC[-15])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])/SUM(RC[-16]:RC[-15])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])/SUM(RC[-16]:RC[-15])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])/SUM(RC[-16]:RC[-15])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])/SUM(RC[-16]:RC[-15])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])/SUM(RC[-16]:RC[-15])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])/SUM(RC[-16]:RC[-15])*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])/SUM(RC[-16]:RC[-15])*100"
        
        Selection.Offset(-20, 1).Select
        ActiveCell.FormulaR1C1 = "=(SUM(RC[-15]:RC[-4])/SUM(RC[-27]:RC[-16]))*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(SUM(RC[-15]:RC[-4])/SUM(RC[-27]:RC[-16]))*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(SUM(RC[-15]:RC[-4])/SUM(RC[-27]:RC[-16]))*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(SUM(RC[-15]:RC[-4])/SUM(RC[-27]:RC[-16]))*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(SUM(RC[-15]:RC[-4])/SUM(RC[-27]:RC[-16]))*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(SUM(RC[-15]:RC[-4])/SUM(RC[-27]:RC[-16]))*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(SUM(RC[-15]:RC[-4])/SUM(RC[-27]:RC[-16]))*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(SUM(RC[-15]:RC[-4])/SUM(RC[-27]:RC[-16]))*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(SUM(RC[-15]:RC[-4])/SUM(RC[-27]:RC[-16]))*100"
        Selection.Offset(4, 0).Select
        ActiveCell.FormulaR1C1 = "=(SUM(RC[-15]:RC[-4])/SUM(RC[-27]:RC[-16]))*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(SUM(RC[-15]:RC[-4])/SUM(RC[-27]:RC[-16]))*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(SUM(RC[-15]:RC[-4])/SUM(RC[-27]:RC[-16]))*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(SUM(RC[-15]:RC[-4])/SUM(RC[-27]:RC[-16]))*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(SUM(RC[-15]:RC[-4])/SUM(RC[-27]:RC[-16]))*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(SUM(RC[-15]:RC[-4])/SUM(RC[-27]:RC[-16]))*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(SUM(RC[-15]:RC[-4])/SUM(RC[-27]:RC[-16]))*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(SUM(RC[-15]:RC[-4])/SUM(RC[-27]:RC[-16]))*100"
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=(SUM(RC[-15]:RC[-4])/SUM(RC[-27]:RC[-16]))*100"         
End Sub

I also have problem with filling last column with appropriate formula. It's year to date comparison, so it should be like SUM(RC[-4]:RC[-3])/SUM(RC[-16]:RC[-15]), increasing by 1 (monthly data) but there need to be loop to allow it only to 12 repeats, then should again start from (RC[-1]/RC[-2])*100.

Could you please help me?
Thank you in advance!

ps: I'm working on Excel 2010.
 
Sort by date Sort by votes
Now I used simple loops to fill those cells:

Code: 
Sub AnalizaTEST()'
    
    
    Cells.Find(what:="Sales Units", After:=ActiveCell, LookIn:=xlFormulas, lookat _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    
    Cells.Find(what:="CP/R1", After:=ActiveCell, LookIn:=xlFormulas, lookat _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    
    Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
        
        Selection.Offset(1, 1).Select
         


' Vstavi formulo v prvi stolpec:
    Do While IsEmpty(ActiveCell.Offset(0, 0)) = False
        ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-2])*100"
        ActiveCell.Offset(1, 0).Select
    Loop
        
' Drugi stolpec:
    Cells.Find(what:="M PY", After:=ActiveCell, LookIn:=xlFormulas, lookat _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    
    Selection.Offset(1, 0).Select
    
' Vstavi formulo v drugi stolpec:
    Do While IsEmpty(ActiveCell.Offset(0, 0)) = False
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-14])*100"
        ActiveCell.Offset(1, 0).Select
    Loop


' Tretji stolpec:
    Cells.Find(what:="BI PY", After:=ActiveCell, LookIn:=xlFormulas, lookat _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    
    Selection.Offset(1, 0).Select
    
' Vstavi formulo v tretji stolpec:
    Do While IsEmpty(ActiveCell.Offset(0, 0)) = False
        ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])/SUM(RC[-16]:RC[-15])*100"
        ActiveCell.Offset(1, 0).Select
    Loop
    
' Četrti stolpec:
    Cells.Find(what:="MAT", After:=ActiveCell, LookIn:=xlFormulas, lookat _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    
    Selection.Offset(1, 0).Select
    
' Vstavi formulo v četrti stolpec:
    Do While IsEmpty(ActiveCell.Offset(0, 0)) = False
        ActiveCell.FormulaR1C1 = "=(SUM(RC[-15]:RC[-4])/SUM(RC[-27]:RC[-16]))*100"
        ActiveCell.Offset(1, 0).Select
    Loop
    
    
' Premik v spodnjo tabelo:


    Cells.Find(what:="Sales Value", After:=ActiveCell, LookIn:=xlFormulas, lookat _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
    Cells.Find(what:="CP/R1", After:=ActiveCell, LookIn:=xlFormulas, lookat _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
   
        Selection.Offset(1, 0).Select
        
' Vstavi formulo v prvi stolpec:
    Do While IsEmpty(ActiveCell.Offset(0, 0)) = False
        ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-2])*100"
        ActiveCell.Offset(1, 0).Select
    Loop
        
' Drugi stolpec:
    Cells.Find(what:="M PY", After:=ActiveCell, LookIn:=xlFormulas, lookat _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    
    Selection.Offset(1, 0).Select
    
' Vstavi formulo v drugi stolpec:
    Do While IsEmpty(ActiveCell.Offset(0, 0)) = False
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-14])*100"
        ActiveCell.Offset(1, 0).Select
    Loop


' Tretji stolpec:
    Cells.Find(what:="BI PY", After:=ActiveCell, LookIn:=xlFormulas, lookat _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    
    Selection.Offset(1, 0).Select
    
' Vstavi formulo v tretji stolpec:
    Do While IsEmpty(ActiveCell.Offset(0, 0)) = False
        ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])/SUM(RC[-16]:RC[-15])*100"
        ActiveCell.Offset(1, 0).Select
    Loop
    
' Četrti stolpec:
    Cells.Find(what:="MAT", After:=ActiveCell, LookIn:=xlFormulas, lookat _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    
    Selection.Offset(1, 0).Select
    
' Vstavi formulo v četrti stolpec:
    Do While IsEmpty(ActiveCell.Offset(0, 0)) = False
        ActiveCell.FormulaR1C1 = "=(SUM(RC[-15]:RC[-4])/SUM(RC[-27]:RC[-16]))*100"
        ActiveCell.Offset(1, 0).Select
    Loop
    
        
     
        End Sub

Is there any way to optimise this?


I also don't know how to fill last column with year to date function. I compares data of year to date nr. of month with same months last year. It is similar as formula in fourth column ((SUM(RC[-15]:RC[-4])/SUM(RC[-27]:RC[-16]))*100)), but it starts with comparing January with January (R-1) and then increases by month.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Create automation VBA code with Power query to load data another workbook
Replies
3
Views
98
hakita
H
T
  • Question Question
ActiveSheet. Paste error
Replies
1
Views
403
RoryA
RoryA
P
  • Question Question
Help with Vba Code
Replies
3
Views
528
Coyotex3
C
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
2K
Joe4
Joe4
J
  • Solved
VBA Code Error Unable to get the Match property of the WorksheetFunction class
Replies
8
Views
703
Jiraya_00
J

Share this page

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
Back
Top