?
Solved

VBA Code

Posted on 2011-02-16
4
Medium Priority
?
194 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

770 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