Solved

VBA Code

Posted on 2011-02-16
4
190 Views
Last Modified: 2012-05-11
Hi,

Attached i have a file with a pivot that changes each day (Screen). In the tab "RC Check" i want to start a daily build up of the two amounts in E121+G121. So i would like a sub that can read those two headings and place them in the next cell down in A+B. I would also like to be able to place the formula in the C+D that would calculate the difference between today and yesterday, and also drop now() into the adjacent cell E

The aim is to see any differences betwee today and yesterday for those sub groups.

Once challenge however is that in the pivot table,  E121+G121 are not static so the figures would need to be read by the Sub Total of Risk and Control, column 5+7.

Thanks!
Seamus
Example.zip
0
Comment
Question by:Seamus2626
[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
  • 2
  • 2
4 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34906484
What formulas do you want in Cell C and D. I will adjust that in the code. Can you give me an example?

For the rest see this sample File. The values will be updated every time you click on the update button.

Sid

Code Used

Private Sub CommandButton1_Click()
    Dim i As Long, lastRowWs1 As Long, nRow As Long
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim SearchString As String
    Dim aCell As Range
    
    Set ws1 = Sheets("RC Check")
    Set ws2 = Sheets("Screen")
    
    SearchString = "Risk and Control Total"
    
    lastRowWs1 = ws1.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    Set aCell = ws2.Cells.Find(What:=SearchString, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then
        nRow = aCell.Row
        ws1.Range("A" & lastRowWs1).Value = ws2.Range("E" & nRow).Value
        ws1.Range("B" & lastRowWs1).Value = ws2.Range("G" & nRow).Value
        ws1.Range("E" & lastRowWs1).Value = Now
    End If
End Sub

Open in new window

Example.xls
0
 

Author Comment

by:Seamus2626
ID: 34906676
thanks Sid!

I just need

C2= A2-A1
D2= B2-B1

C3=A3-A2
D3=B3-B2

etc

Thanks!
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 34906725
Try this.

Private Sub CommandButton1_Click()
    Dim i As Long, lastRowWs1 As Long, nRow As Long
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim SearchString As String
    Dim aCell As Range
    
    Set ws1 = Sheets("RC Check")
    Set ws2 = Sheets("Screen")
    
    SearchString = "Risk and Control Total"
    
    lastRowWs1 = ws1.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    Set aCell = ws2.Cells.Find(What:=SearchString, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then
        nRow = aCell.Row
        ws1.Range("A" & lastRowWs1).Value = ws2.Range("E" & nRow).Value
        ws1.Range("B" & lastRowWs1).Value = ws2.Range("G" & nRow).Value
        ws1.Range("E" & lastRowWs1).Value = Now
        If lastRowWs1 <> 2 Then
            ws1.Range("C" & lastRowWs1).FormulaR1C1 = "=RC[-2]-R[-1]C[-2]"
            ws1.Range("D" & lastRowWs1).FormulaR1C1 = "=RC[-2]-R[-1]C[-2]"
        End If
    End If
End Sub

Open in new window


Sid
0
 

Author Closing Comment

by:Seamus2626
ID: 34906759
Absolutely perfect Sid!!

Thank you so much

Seamus
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

696 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