summarize topsheet using vba formula string to reference cells from other sheets

[monoj chakraborty]

Hey good people,

I am trying to design a simple algorithm, which should summarize individual worksheets in a workbook into a topsheet.

The code I have tried to write is as follows :

Sub consolidate()
'
' pulldata Macro
'

'

Dim asmsheets As Integer 'iterator for looping thru' individual sheets
Dim Topsheetrow As Integer 'select rows for pulling data from asm sheets
Dim Topsheetcol As Integer 'select columns for pulling data from asm sheets
Dim formulastring As String 'string for formula to pull data from the relevant cells from the asm sheets
Dim chooseasm As Integer
Dim asmsheetcol As Integer

Regions = Array("Homanjit", "Alok", "Jitendra", "Vijay", "Karthikeyan") 'array to store asm individual sheets

Worksheets("Topsheet").Activate

For Topsheetrow = 3 To 7
    For Topsheetcol = 2 To 7
        Cells(Topsheetrow, Topsheetcol).Select
            For chooseasm = 0 To 4
                Asm = Regions(chooseasm)
                          Select Case Topsheetcol
                                    
                                    Case Is = 2
                                    
                                    asmsheetcol = 4
                                    
                                    Case Is = 3
                                    
                                    asmsheetcol = 13
                                    
                                    Case Is = 4
                                    
                                    asmsheetcol = 14
                                    
                                    Case Is = 5
                                    
                                    asmsheetcol = 15
                                    
                                    Case Is = 6
                                    
                                    asmsheetcol = 24
                                    
                                    Case Is = 7
                                    
                                    asmsheetcol = 25
                                    
                                    End Select
            
            formulastring = "=Asm & " !R" & 33-topsheetrow & "C" & asmsheetcol-topsheetcol"
            
            ActiveCell.FormulaR1C1 = formulastring
            
            Next chooseasm
        
        Next Topsheetcol
                
Next Topsheetrow
        
End Sub

I have attached the file for quick reference and the code above is under module1 - unfortunately, while the code runs without an error, the values returned are all "FALSE".

I know it has to do with the formula string but am not able to pin it down.

Any help much appreciated in advance

best regds.,

 

[Marc L]

Hi, as there is a codeline in red - bad syntax ! - so the VBA procedure can't be ever executed so we can't see any FALSE value …​

​

 

[monoj chakraborty]

Hi, as there is a codeline in red - bad syntax ! - so the VBA procedure can't be ever executed so we can't see any FALSE value …​

​

Hi @Marc L ,

Thanks for the quick revert...

I know somewhere the code is breaking - most probably at the formula string line. But I am no able to write the formula correctly in R1C1 mode. Is there any suggestion on how to rewrite this correctly in the proper syntax and clean this up?

Thanks a ton.

regds.,

 

[Marc L]

As you wrote « while the code runs without an error » which can very not be true so​

we wanna know which code did you really run as it can't be the one from your initial post !​

​

Rather than any guessing challenge which has no place whatever the Excel forum​

an attachment containing the original formulas in cells and accordingly an accurate elaboration should help …​

​

 

[monoj chakraborty]

Dear @Marc L ,

I revised the code as follows :

Sub consolidate()
'
' pulldata Macro
'

'

Dim asmsheets As Integer 'iterator for looping thru' individual sheets
Dim Topsheetrow As Integer 'select rows for pulling data from asm sheets
Dim Topsheetcol As Integer 'select columns for pulling data from asm sheets
Dim formulastring As String 'string for formula to pull data from the relevant cells from the asm sheets
'Dim chooseasm As Integer
Dim asmsheetcol As Integer

regions = Array("Homanjit", "Alok", "Jitendra", "Vijay", "Karthikeyan") 'array to store asm individual sheets

Worksheets("Topsheet").Activate

For Topsheetrow = 3 To 7
    
    For Topsheetcol = 2 To 7
        
        Cells(Topsheetrow, Topsheetcol).Select
            
           Select Case Topsheetrow
          
                Case Is = 3
                
                     asm = regions(0)
                
                Case Is = 4
                                        
                     asm = regions(1)
                
                Case Is = 5
                
                     asm = regions(2)
                
                Case Is = 6
                                        
                     asm = regions(3)
            
                Case Is = 7
                                            
                    asm = regions(4)
            
            End Select
            
            Select Case Topsheetcol
                
                Case Is = 2
                
                asmsheetcol = 4
                
                Case Is = 3
                
                asmsheetcol = 13
                
                Case Is = 4
                
                asmsheetcol = 14
                
                Case Is = 5
                
                asmsheetcol = 15
                
                Case Is = 6
                
                asmsheetcol = 24
                
                Case Is = 7
                
                asmsheetcol = 25
                
            End Select
            
        formulastring = ""=+" & asm & "!R" & [23] & "C" [2]"
        
        ActiveCell.FormulaR1C1 = formulastring
            
        Next Topsheetcol
                
Next Topsheetrow
        
End Sub

The row and column number in the formula string are only for illustrative purpose - the code returns an error at this line. If I get the formula string correct I will change the row and column numbers to variables as the code loops thru'.

If I use the immediate window to check the formula string in the activecell, this is what I get :

=+Homanjit!R[30]C[2]

I have used the exact same syntax within the string in the main code.

If I run the code with the string as above, it works (worksheet attached), except, the sheetname does not change, which is what I want to achieve thru' the code.

 

[Marc L]

You must replace the variable part of the string with a VBA Variable like "=" & asm & "!R…" for the worksheet name …​

​

 

[monoj chakraborty]

That worked @Marc L . Thanks