Solved

VBA Code

Posted on 2011-02-16
4
177 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
  • 2
  • 2
4 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Absolutely perfect Sid!!

Thank you so much

Seamus
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

771 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

10 Experts available now in Live!

Get 1:1 Help Now