Solved

Simply RC code in VBA

Posted on 2011-03-21
14
579 Views
Last Modified: 2012-05-11
Hi,

can anyone help me simplify this type of code, so I do not have to mention each cell. I used to know how to work it around with the parameters but people tend to forget. I am doing a cumulative of several workbooks which have sheets named the same way. The formula goes to search to the same folder everytime. How to make it simple - look into each workbook and look for relevant data in a relevant cell? Any help very much appreciated. Thank you much!
Range("D12").Select
    ActiveCell.FormulaR1C1 = _
        "='[2t13.xlsx]1630.001 Montáž koles.jednot.'!R12C4+'[2t23.xlsx]1630.001 Montáž koles.jednot.'!R12C4+'[2t33.xlsx]1630.001 Montáž koles.jednot.'!R12C4+'[2t43.xlsx]1630.001 Montáž koles.jednot.'!R12C4+'[2t73.xlsx]1630.001 Montáž koles.jednot.'!R12C4+'[2t83.xlsx]1630.001 Montáž koles.jednot.'!R12C4+'[2t93.xlsx]1630.001 Montáž koles.jednot.'!R12C4+'[2t103.xlsx]1630.001 Montáž koles.jednot.'!R12C4+'[2t113.xlsx]1630.001 Montáž koles.jednot.'!R12C4+'[2t143.xlsx]1630.001 Montáž koles.jednot.'!R12C4+'[2t153.xlsx]1630.001 Montáž koles.jednot.'!R12C4+'[2t163.xlsx]1630.001 Montáž koles.jednot.'!R12C4+'[2t173.xlsx]1630.001 Montáž koles.jednot.'!R12C4"
    Range("D13").Select
    ActiveCell.FormulaR1C1 = _
        "='[2t13.xlsx]1630.001 Montáž koles.jednot.'!R13C4+'[2t23.xlsx]1630.001 Montáž koles.jednot.'!R13C4+'[2t33.xlsx]1630.001 Montáž koles.jednot.'!R13C4+'[2t43.xlsx]1630.001 Montáž koles.jednot.'!R13C4+'[2t73.xlsx]1630.001 Montáž koles.jednot.'!R13C4+'[2t83.xlsx]1630.001 Montáž koles.jednot.'!R13C4+'[2t93.xlsx]1630.001 Montáž koles.jednot.'!R13C4+'[2t103.xlsx]1630.001 Montáž koles.jednot.'!R13C4+'[2t113.xlsx]1630.001 Montáž koles.jednot.'!R13C4+'[2t143.xlsx]1630.001 Montáž koles.jednot.'!R13C4+'[2t153.xlsx]1630.001 Montáž koles.jednot.'!R13C4+'[2t163.xlsx]1630.001 Montáž koles.jednot.'!R13C4+'[2t173.xlsx]1630.001 Montáž koles.jednot.'!R13C4"
    Range("K12").Select
    ActiveCell.FormulaR1C1 = _
        "='[2t13.xlsx]1630.001 Montáž koles.jednot.'!R12C11+'[2t23.xlsx]1630.001 Montáž koles.jednot.'!R12C11+'[2t33.xlsx]1630.001 Montáž koles.jednot.'!R12C11+'[2t43.xlsx]1630.001 Montáž koles.jednot.'!R12C11+'[2t73.xlsx]1630.001 Montáž koles.jednot.'!R12C11+'[2t83.xlsx]1630.001 Montáž koles.jednot.'!R12C11+'[2t93.xlsx]1630.001 Montáž koles.jednot.'!R12C11+'[2t103.xlsx]1630.001 Montáž koles.jednot.'!R12C11+'[2t113.xlsx]1630.001 Montáž koles.jednot.'!R12C11+'[2t143.xlsx]1630.001 Montáž koles.jednot.'!R12C11+'[2t153.xlsx]1630.001 Montáž koles.jednot.'!R12C11+'[2t163.xlsx]1630.001 Montáž koles.jednot.'!R12C11+'[2t173.xlsx]1630.001 Montáž koles.jednot.'!R12C11"
    Range("K13").Select
    ActiveCell.FormulaR1C1 = _
        "='[2t13.xlsx]1630.001 Montáž koles.jednot.'!R13C11+'[2t23.xlsx]1630.001 Montáž koles.jednot.'!R13C11+'[2t33.xlsx]1630.001 Montáž koles.jednot.'!R13C11+'[2t43.xlsx]1630.001 Montáž koles.jednot.'!R13C11+'[2t73.xlsx]1630.001 Montáž koles.jednot.'!R13C11+'[2t83.xlsx]1630.001 Montáž koles.jednot.'!R13C11+'[2t93.xlsx]1630.001 Montáž koles.jednot.'!R13C11+'[2t103.xlsx]1630.001 Montáž koles.jednot.'!R13C11+'[2t113.xlsx]1630.001 Montáž koles.jednot.'!R13C11+'[2t143.xlsx]1630.001 Montáž koles.jednot.'!R13C11+'[2t153.xlsx]1630.001 Montáž koles.jednot.'!R13C11+'[2t163.xlsx]1630.001 Montáž koles.jednot.'!R13C11+'[2t173.xlsx]1630.001 Montáž koles.jednot.'!R13C11"

Open in new window

0
Comment
Question by:lejohney
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35186951
To understand it let's take this part

R12C4

This signifies Row 12 and Column 4. Which cell do you think this refers to :)

Also

'[2t13.xlsx]1630.001 Montáž koles.jednot.'

comprises of two things

1) The workbook which is "2t13.xlsx" AND
2) Sheet Name which is "1630.001 Montáž koles.jednot."

Now to simplify it one approach would be to open the workbook and get the value from each workbook and sum it and finally store it in Range("D12"). Is that what you want?

Sid
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35187156
Your formulas are each basically the same apart from the row/column numbers, but they are the same as the cells you are putting the formula into, so you can use relative numbers:
Range("D12:D13,K12:K13").FormulaR1C1 = _  
        "='[2t13.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t23.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t33.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t43.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t73.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t83.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t93.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t103.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t113.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t143.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t153.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t163.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t173.xlsx]1630.001 Montáž koles.jednot.'!RC"  

Open in new window

0
 

Author Comment

by:lejohney
ID: 35190318
Thank you guys, I get your point.

The thing now is, how to make the referencing more simple so the code does not look as horrific, as it is presented here below.
I have a list of worksheets that are the same for each workbook. I got the list via a VBA code. Now I would like to change the referencing of the sheet names through using of a relevant cell with the sheet name as reference. I also have a list of files (in cells) that are included in the code (like 2t23.xlsx in CELL B2 in worksheet Sheet 1). Can I shorten the code with such referencing? Please advice. Thank you!
'Case 1
Sheets("1630.001 Montáž koles.jednot.").Activate
Range("D12:AG37").FormulaR1C1 = _
        "='[2t13.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t23.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t33.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t43.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t73.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t83.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t93.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t103.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t113.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t143.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t153.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t163.xlsx]1630.001 Montáž koles.jednot.'!RC+'[2t173.xlsx]1630.001 Montáž koles.jednot.'!RC"
'Case 2
Sheets("1241.002 Lis MATRA").Activate
Range("D12:AG34").FormulaR1C1 = _
        "='[2t13.xlsx]1241.002 Lis MATRA'!RC+'[2t23.xlsx]1241.002 Lis MATRA'!RC+'[2t33.xlsx]1241.002 Lis MATRA'!RC+'[2t43.xlsx]1241.002 Lis MATRA'!RC+'[2t73.xlsx]1241.002 Lis MATRA'!RC+'[2t83.xlsx]1241.002 Lis MATRA'!RC+'[2t93.xlsx]1241.002 Lis MATRA'!RC+'[2t103.xlsx]1241.002 Lis MATRA'!RC+'[2t113.xlsx]1241.002 Lis MATRA'!RC+'[2t143.xlsx]1241.002 Lis MATRA'!RC+'[2t153.xlsx]1241.002 Lis MATRA'!RC+'[2t163.xlsx]1241.002 Lis MATRA'!RC+'[2t173.xlsx]1241.002 Lis MATRA'!RC"

'Case 3
Sheets("1640.001 Montáž KV_KR").Activate
Range("D12:AG34").FormulaR1C1 = _
        "='[2t13.xlsx]1640.001 Montáž KV_KR'!RC+'[2t23.xlsx]1640.001 Montáž KV_KR'!RC+'[2t33.xlsx]1640.001 Montáž KV_KR'!RC+'[2t43.xlsx]1640.001 Montáž KV_KR'!RC+'[2t73.xlsx]1640.001 Montáž KV_KR'!RC+'[2t83.xlsx]1640.001 Montáž KV_KR'!RC+'[2t93.xlsx]1640.001 Montáž KV_KR'!RC+'[2t103.xlsx]1640.001 Montáž KV_KR'!RC+'[2t113.xlsx]1640.001 Montáž KV_KR'!RC+'[2t143.xlsx]1640.001 Montáž KV_KR'!RC+'[2t153.xlsx]1640.001 Montáž KV_KR'!RC+'[2t163.xlsx]1640.001 Montáž KV_KR'!RC+'[2t173.xlsx]1640.001 Montáž KV_KR'!RC"

'Case 4
Sheets("1650.004_KHD,DTBT_DTBA").Activate
Range("D12:AG34").FormulaR1C1 = _
        "='[2t13.xlsx]1650.004_KHD,DTBT_DTBA'!RC+'[2t23.xlsx]1650.004_KHD,DTBT_DTBA'!RC+'[2t33.xlsx]1650.004_KHD,DTBT_DTBA'!RC+'[2t43.xlsx]1650.004_KHD,DTBT_DTBA'!RC+'[2t73.xlsx]1650.004_KHD,DTBT_DTBA'!RC+'[2t83.xlsx]1650.004_KHD,DTBT_DTBA'!RC+'[2t93.xlsx]1650.004_KHD,DTBT_DTBA'!RC+'[2t103.xlsx]1650.004_KHD,DTBT_DTBA'!RC+'[2t113.xlsx]1650.004_KHD,DTBT_DTBA'!RC+'[2t143.xlsx]1650.004_KHD,DTBT_DTBA'!RC+'[2t153.xlsx]1650.004_KHD,DTBT_DTBA'!RC+'[2t163.xlsx]1650.004_KHD,DTBT_DTBA'!RC+"

Open in new window

0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 46

Expert Comment

by:aikimark
ID: 35190688
If you created a named range in the source workbook, you could shorten the name considerably.

Examples:
In the following formula in the Book1 workbook, I refer to a single cell in the NR named range in Book2
=OFFSET(Book2!NR,0,0,1,1)

Open in new window


Since the Offset() function allows me to address a range, I can show the sum of multiple cells in the named range.  In this case, I'm summing the first four cells -- in the first two rows and first two columns.
=SUM(OFFSET(Book2!NR,0,0,2,2))

Open in new window

0
 
LVL 46

Expert Comment

by:aikimark
ID: 35190791
In your source workbooks, you might create named ranges for
1630.001 Montáž koles.jednot
1241.002 Lis MATRA
1640.001 Montáž KV_KR
1650.004_KHD,DTBT_DTBA

Open in new window


since it is the names of the workbooks that changes in the source reference, rather than the locations within a worksheet.
0
 

Author Comment

by:lejohney
ID: 35191718
Hi Aikimark,

thank you much for your comment. However I think it does not solve my problem completely. The code says it. Do I have to create the named ranges in all of my 20 workbooks? I need to sum up sheet by sheet from 20 workbooks into one master. I am not sure whether by doing it your way would be much easier. Can you provide me with a complete sample code so I understand the thing better? Here I provide a sample of what I am doing. You can help me by showing on this sample sheet. I have 20 of them like that and one master. Sample.xlsm
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35192064
>>I need to sum up sheet by sheet from 20 workbooks into one master

Then you probably want a non-formula solution.

Maybe you might want to use the code/methods from this article:
http://www.experts-exchange.com/A_2804.html

If the article isn't a solution, we can work on a solution in which each of the 20 workbooks is opened and the contents of the 23x30 cell range are summed.

The fastest approach would be to do the summing in an array and then push the results one time.
http://www.experts-exchange.com/A_2253.html

However, copy/paste special operations can also do the summing during the paste.
0
 

Author Comment

by:lejohney
ID: 35192421
The "collate" code seems to be OK for the thing. The truth is I have not a clue how to work it around this case. Maybe your help on the file I sent would help ...a lot. If you could spare some minutes I would appreciate it greatly. From there I am sure I will be able to pick up. Thank you very much
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35192800
The article's ConsolidateSheets() code would need to be changed a couple of ways.  First, the xlPasteSpecialOperationAdd parameter would be used and then the check for a preexisting worksheet would need to be eliminated.  I'm going to contact the author to see if he would like to incorporate that code change.

Your code is pretty close.  I've formatted it below.  I'll need to spend some time with the code to incorporate the summing.  Before I begin that, what is the relationship between "CLOOS-1323.003" and "630.001 Montáž koles.jednot."?  I suspect I would understand it if I knew the language.

Also, are there going to be other worksheets in the source workbooks than the ones you already referenced in your formulas?
Sub Summary()

'Summarization macro - is searching for a specific cell in every workbook in the folder and sums it up in a predefined cell

  Dim wb As Workbook, TheFile As String
  Dim MyPath As String, TheSum As Double
  Dim M As Range
  MyArray = Array("CLOOS-1323.003", "CLOOS-1324.004", "CLOOS-1325.005")
  TheSum = 0
  MyPath = ThisWorkbook.Path
  ChDir MyPath
  TheFile = Dir("*.xlsx")
  Do While TheFile <> ""
    If TheFile <> ThisWorkbook.Name Then
      Set wb = Workbooks.Open(MyPath & "\" & TheFile)
      TheSum = TheSum + Sheets("1630.001 Montáž koles.jednot.").Range("G12:028")
      wb.Close
    End If
    TheFile = Dir
  Loop
  Sheets(MyArray).Range("G12:028") = TheSum
End Sub

Open in new window

0
 
LVL 46

Expert Comment

by:aikimark
ID: 35194870
Notes:
The sheet names in the Array() function did not match the worksheet names in the uploaded file
For testing purposes, I made copies of the XLSM file you uploaded, thus the code only looks for *.xlsm files in the DIR() function.
When this code is run, it assumes that the current workbook as worksheets with the same name as the source workbooks and the MyArray

Option Explicit

Sub Summary()

'Summarization macro - is searching for a specific cell in every workbook in the folder and sums it up in a predefined cell

  Dim wb As Workbook, TheFile As String, MyArray() As Variant
  Dim MyPath As String, TheSum As Double
  Dim M As Range, wksThing As Worksheet
  Dim vThisSheetData As Variant, vSheetSumData() As Long
  MyArray = Array("CLOOS -1323.003", "CLOOS -1324.004 ", "CLOOS -1325.005 ")  '<-- changed  to match uploaded file worksheet names
  Dim lngIndex As Long, dicSheetNames As Object
  Dim lngRow As Long, lngCol As Long, boolFound As Boolean
  Set dicSheetNames = CreateObject("Scripting.Dictionary")
  ReDim vSheetSumData(1 To 23, 1 To 30, LBound(MyArray) To UBound(MyArray))
  For lngIndex = LBound(MyArray) To UBound(MyArray)
    dicSheetNames.Add MyArray(lngIndex), lngIndex
  Next
  
  MyPath = ThisWorkbook.Path
  
  TheFile = Dir("*.xlsm")  '<-- changed for testing
  Do While Len(TheFile) <> 0
    If TheFile <> ThisWorkbook.Name Then
      Set wb = Workbooks.Open(MyPath & "\" & TheFile, False, True)
      For Each wksThing In wb.Worksheets
        If dicSheetNames.Exists(wksThing.Name) Then
          lngIndex = dicSheetNames(wksThing.Name)
          vThisSheetData = wksThing.Range("D12:AG34").Value
          For lngRow = LBound(vThisSheetData, 1) To UBound(vThisSheetData, 1)
            For lngCol = LBound(vThisSheetData, 2) To UBound(vThisSheetData, 2)
              vSheetSumData(lngRow, lngCol, lngIndex) = vSheetSumData(lngRow, lngCol, lngIndex) + vThisSheetData(lngRow, lngCol)
            Next
          Next
        
        End If
      Next
      wb.Close
    End If
    TheFile = Dir
  Loop
  
  For lngIndex = LBound(MyArray) To UBound(MyArray)
    For lngRow = LBound(vSheetSumData, 1) To UBound(vSheetSumData, 1)
      For lngCol = LBound(vSheetSumData, 2) To UBound(vSheetSumData, 2)
        vThisSheetData(lngRow, lngCol) = vSheetSumData(lngRow, lngCol, lngIndex)
      Next
    Next
    Worksheets(MyArray(lngIndex)).Range("D12:AG34").Value = vThisSheetData
  Next
End Sub

Open in new window

0
 

Author Comment

by:lejohney
ID: 35196803
Hi Aikimark,

thank you for the code! I tried to run it but it does not sum up values.
Let's see what I've done:

After your reply I created a folder TESTING MACRO. I copied 3 times the sample workbook renaming it 1,2,3. I kept the sample WB in the same folder and ran the macro but it only retrieved me 0 s and 1 s instead of summing up to three in the relevant rows. Could you please check where I am doing a bad job?
Thank you very much for the challenge.
0
 
LVL 46

Accepted Solution

by:
aikimark earned 500 total points
ID: 35197999
@lejohney

I tested the code I posted in the manner you described and got a mixture of 0 and summed values in all three worksheets.

Please post the two workbooks you used (one with code as the summary target and the other used as the data sources)

====
Since this is a duplicate question and the duplicate has been answered, does this question thread still have information value for you?
0
 

Author Closing Comment

by:lejohney
ID: 35384819
great job
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

634 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question