• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Looking for a more elegant solution

Emeng

Member
Hi all

I have created a macro to retrieve data from selected sheets to a summary page in the same workbook. The code is fine but took some time to put together. I'm wondering if is there is a simpler solution. The patterns are easy to follow. Thanks for your help

Mark
Code:
Sub SetFormulas()
Const s = "Sheet1"
  
    With Sheets(s)
        
        .Range("E5") = Sheets(3).Range("G3").Value  'no. of FuncLocs
        .Range("E6") = Sheets(4).Range("G3").Value
        .Range("E7") = Sheets(5).Range("G3").Value
        .Range("E8") = Sheets(6).Range("G3").Value
        .Range("E9") = Sheets(7).Range("G3").Value
        .Range("E10") = Sheets(8).Range("G3").Value
        .Range("E11") = Sheets(9).Range("G3").Value
        .Range("E12") = Sheets(10).Range("G3").Value
        .Range("E13") = Sheets(11).Range("G3").Value
        .Range("E14") = Sheets(12).Range("G3").Value
        .Range("E15") = Sheets(13).Range("G3").Value
        .Range("E16") = Sheets(14).Range("G3").Value
        .Range("E17") = Sheets(15).Range("G3").Value
        .Range("E18") = Sheets(16).Range("G3").Value
        .Range("E19") = Sheets(17).Range("G3").Value
        .Range("E20") = Sheets(18).Range("G3").Value
      
        .Range("F5").Formula = "='3'!$J$3"    ''Total no. of BOMs
        .Range("F6").Formula = "='4'!$J$3"
        .Range("F7").Formula = "='5'!$J$3"
        .Range("F8").Formula = "='6'!$J$3"
        .Range("F9").Formula = "='7'!$J$3"
        .Range("F10").Formula = "='8'!$J$3"
        .Range("F11").Formula = "='9'!$J$3"
        .Range("F12").Formula = "='10'!$J$3"
        .Range("F13").Formula = "='11'!$J$3"
        .Range("F14").Formula = "='12'!$J$3"
        .Range("F15").Formula = "='13'!$J$3"
        .Range("F16").Formula = "='14'!$J$3"
        .Range("F17").Formula = "='15'!$J$3"
        .Range("F18").Formula = "='16'!$J$3"
        .Range("F19").Formula = "='17'!$J$3"
        .Range("F20").Formula = "='18'!$J$3"
 
Code:
Sub SetFormulas()
    Const s = "Sheet1"

    With Sheets(s)
        For n = 1 To 15
            .Range("E" & n + 4) = Sheets(n + 2).Range("G3").Value
            '            .Range("E5") = Sheets(3).Range("G3").Value  'no. of FuncLocs
            '            .Range("E20") = Sheets(18).Range("G3").Value

            .Range("F" & n + 4).Formula = "='" & n + 2 & "'!$J$3"
            '            .Range("F5").Formula = "='3'!$J$3"    ''Total no. of BOMs
            '            .Range("F20").Formula = "='18'!$J$3"
        Next
    End With
End Sub
 
Oh that's great!

Thanks Ed. I'll take some time to study this for my own understanding.

Just to clarify, 'n' would be 'As Integer'?

Regards Mark
 
how can I say, I never bothered myself
when I declare variables are for use of large loops
mostly I use long even below that only for specific use or arrays
and above, it is for calculations,
 
If you are concerned about the speed of macro
the ideal is to first pass the values to a variant array
and then pass the array to the worksheet, this will greatly improve the performance of the macro, mostly in the formulas

Code:
Sub SetFormulas()
    Const s = "Sheet1"
  
    Dim Array1()
      ReDim Array1(1 To 15, 1 To 2)
    With Sheets(s)
        For n = 1 To 15
            Array1(n, 1) = Sheets(n + 2).Range("G3").Value2
            Array1(n, 2) = "='" & n + 2 & "'!$J$3"
        Next
        .Range("E5:F20").formula = Array1
    End With
End Sub
 
Last edited:
Hi Ed

TBH I'm more worried about the speed of writing the macro at the moment!
Your last comment was a bit over my head. When I get more understanding I'll come back to it and understand you better. I have another formula as below which I have attempted to adapt. Am I close?
Code:
        Range("F26").Formula = "=E23-E26"
        Range("F27").Formula = "=F23-E27"
        Range("F28").Formula = "=G23-E28"
 
.Range("F" & n + 26).Formula = "=" & l + 4 & "E" & n + 22
 
this approach only works for sequences, 1,2,3 ...
for irregular sequences one has to study the logic, or change the concept of data

Range("F27").Formula = "=F23-E27"
Range("F28").Formula = "=G23-E28"

N + x
n = 1 to the data todal
line = 26 to 100
(n + 25)

OR
Range("F26").Formula = "=E23-E26"
Range("F126").Formula = "=E23-E126"

n=26 to 126
Range("F" & n ).Formula = "=E23-E" & n
 
Last edited:
So if I follow you correctly... only 1 variable allowed by this method... rows or columns, not both.
 
Hi !​
Code:
    [F26:F28].Formula = "=OFFSET(E$23,0,ROW()-26)-E26"
Do you like it ? So thanks to click on bottom right Like !
 
Yes, both can, but have to follow the logic
Range ("F26"). Formula = "= E23-E26"
Range ("F27"). Formula = "= F23-E27"
Range ("F28"). Formula = "= G23-E28"

.range ("F" & n + 26) .Formula = "=" & l + 4 & "E" & n + 22
if n starts as 1 and the cell # 26
.Range("F" & n + 25).Formula
1 +25 =26

"=E23-E26"
"=" & l + 4 & "E" & n + 22 no
=letter +n-1 & "23-E" & n+25

to the letter have to use a function to convert

Code:
Public Function Letra_Col(ByVal Numero_Coluna As Long) As String    ' '-------(Transforma numero  em letra de coluna)
    Dim CLx As String
    CLx = Cells(1, Numero_Coluna).Address
    Letra_Col = Mid(CLx, InStr(CLx, "$") + 1, InStr(2, CLx, "$") - 2)
End Function

Range ("F26"). Formula = "= E23-E26"
initial_letter=cells(1,"E").column

.Range("F" & n + 25).Formula = "=" & Letra_Col(initial_letter+n-1) & "23-E" & n+25
 
Last edited:
Yes, I follow you now. You have been very helpful.
Obrigado muito por seu tempo e persistência.
Regards
Mark
 
Code:
Sub SetFormulas()
Const s = "Sheet1"

    With Sheets(s)
      
        .Range("E5") = Sheets(3).Range("G3").Value  'no. of FuncLocs
        .Range("E6") = Sheets(4).Range("G3").Value
        .Range("E7") = Sheets(5).Range("G3").Value
        .Range("E8") = Sheets(6).Range("G3").Value
        .Range("E9") = Sheets(7).Range("G3").Value
        .Range("E10") = Sheets(8).Range("G3").Value
        .Range("E11") = Sheets(9).Range("G3").Value
        .Range("E12") = Sheets(10).Range("G3").Value
        .Range("E13") = Sheets(11).Range("G3").Value
        .Range("E14") = Sheets(12).Range("G3").Value
        .Range("E15") = Sheets(13).Range("G3").Value
        .Range("E16") = Sheets(14).Range("G3").Value
        .Range("E17") = Sheets(15).Range("G3").Value
        .Range("E18") = Sheets(16).Range("G3").Value
        .Range("E19") = Sheets(17).Range("G3").Value
        .Range("E20") = Sheets(18).Range("G3").Value
    
        .Range("F5").Formula = "='3'!$J$3"    ''Total no. of BOMs
        .Range("F6").Formula = "='4'!$J$3"
        .Range("F7").Formula = "='5'!$J$3"
        .Range("F8").Formula = "='6'!$J$3"
        .Range("F9").Formula = "='7'!$J$3"
        .Range("F10").Formula = "='8'!$J$3"
        .Range("F11").Formula = "='9'!$J$3"
        .Range("F12").Formula = "='10'!$J$3"
        .Range("F13").Formula = "='11'!$J$3"
        .Range("F14").Formula = "='12'!$J$3"
        .Range("F15").Formula = "='13'!$J$3"
        .Range("F16").Formula = "='14'!$J$3"
        .Range("F17").Formula = "='15'!$J$3"
        .Range("F18").Formula = "='16'!$J$3"
        .Range("F19").Formula = "='17'!$J$3"
        .Range("F20").Formula = "='18'!$J$3"
You should Like this :​
Code:
    With Sheet1
        .[E5:E20].Formula = "=INDIRECT(""'"" & ROW()-2 & ""'!$G$3"")"
'       .[E5:E20].Formula = .[E5:E20].Value
         .[F5:F20].Formula = "=INDIRECT(""'"" & ROW()-2 & ""'!$J$3"")"
 
That's interesting... I've not used INDIRECT before.
Time to go have a play with it!
Thanks & regards
Mark
 
Back
Top