Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VBA Code

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

604 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