Solved

Simply RC code in VBA

Posted on 2011-03-21
14
554 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
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
 
LVL 45

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 45

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 45

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 45

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 45

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 45

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now