CODEWITHSUNDEEP
vbaexcel
Elliott
Elliott

Reputation: 25

Cell Cross-Reference using VBA

I have done some searching and cannot figure out the syntax for what I would like to accomplish. I would like to have a specific, static cell on a Summary sheet automatically update to a dynamic cell on a different worksheet. I started with the following code:

Sheets("Summary").Activate
Range("B1").Select
ActiveCell.FormulaR1C1 = "='Current Billing'!R[46]C"
Range("B2").Select
ActiveCell.FormulaR1C1 = _
    "='Current Billing'!R[45]C[3]+'Current Billing'!R[45]C[4]"

After getting some advice on a different topic for this same workbook, I now know .Select is a no-no and should be avoided. I also know that my last row on the "Current Billing" worksheet will change when I copy the code to another workbook for billing that project. This has led to me to modify my code to make it more fool-proof and versatile.

I have figured out how to get the the cell value to insert from my "Current Billing" sheet to my "Summary" sheet. But if something changes on the "Current Billing" sheet, the "Summary" sheet will not automatically update. This is the code I have that sort of works:

Dim ws5 As Worksheet
'ws5 is "Current Billing"
Dim ws6 As Worksheet
'ws6 is "Summary"
Dim LRowB2 As Long
Dim LRowB3 As String
LRowB2 = ws5.Cells(Rows.Count, "B").End(xlUp).Row
LRowB3 = ws5.Cells(LRowB2, "B")
ws6.Cells(1, "B").Formula = LRowB3

I tried these two code sequences, but kept getting errors:

Dim LRowB4 As String
Dim LRowE2 As Long
Dim LRowF2 As Long

LRowB4 = "= & ws5 & ! & LRowB3"
ws6.Cells(2, "B").Formula = "=sum(& LRowE2 &,& LRowF2 &)"

In short, is there a way to mimic the function of the first code procedure, but have the stability and fool-proofness of the second procedure? And, is there a way to integrate a =Sum() formula into the second procedure so I can add two cells from "Current Billing" on the "Summary" page? Thanks for the help!

Upvotes: 2

Views: 1234

Answers (2)

paul bica
paul bica

Reputation: 10715

The syntax of the formula is similar to =SUM('Current Billing'!C9, 'Current Billing'!D9)

This will check for errors, and is a bit more dynamic

Option Explicit

Public Sub UpdateSummary()
    Dim wsCB As Worksheet, lrCB As Long, cbCellC As Range, cbCellD As Range
    Dim wsSummary As Worksheet, sumCell As Range

    Set wsCB = ThisWorkbook.Worksheets("Current Billing")
    Set wsSummary = ThisWorkbook.Worksheets("Summary")

    lrCB = wsCB.Cells(wsCB.Rows.Count, "C").End(xlUp).Row

    'Verify that we have the same last row for col C and D on sheet "Current Billing"
    If wsCB.Cells(wsCB.Rows.Count, "D").End(xlUp).Row = lrCB Then
        Set cbCellC = wsCB.Cells(lrCB, "C")     '"Current Billing".Cell(lr, C)
        Set cbCellD = wsCB.Cells(lrCB, "D")     '"Current Billing".Cell(lr, D)
        Set sumCell = wsSummary.Cells(2, "B")   '"Summary".Cell(2, B)
        sumCell = "Invalid 'Current Billing' values"    'Default (in case of errors)

        'Check "Current Billing" cells for errors (#N/A, #VALUE!, #REF!, #DIV/0!, etc)
        If Not IsError(cbCellC) And Not IsError(cbCellD) Then

            'Verify that "Current Billing" cells are numbers
            If IsNumeric(cbCellC) And IsNumeric(cbCellD) Then
                Dim cbC As String, cbD As String

                cbC = "'" & wsCB.Name & "'!" & cbCellC.Address(0, 0) 'Current Billing'!C9
                cbD = "'" & wsCB.Name & "'!" & cbCellD.Address(0, 0) 'Current Billing'!D9

                'Final format: =SUM('Current Billing'!C9, 'Current Billing'!D9)

                sumCell.Formula = "=SUM(" & cbC & ", " & cbD & ")"  'Update Summary cell
            End If
        End If
    End If
End Sub

If the formula will contain a division, verify that the divisor is not 0

But if you are using VBA for this, you can simplify the syntax: sumCell = cbCellC + cbCellD

Note:

String variables become more complex when we have to use quotes inside quotes:

  • Str with ""double quotes"" can be built like this

    • str = "Str with """"double quotes""""", or
    • str = "Str with " & Chr(34) & """double quotes""" & Chr(34), or
    • str = "Str with " & Chr(34) & Chr(34) & "double quotes" & Chr(34) & Chr(34)

  • This string str = "Str with 'double quotes'" is Str with 'double quotes'

Upvotes: 1

ChrisB
ChrisB

Reputation: 3205

Use the Workbook_SheetChange event. It's an "event" action that is triggered when changes are made anywhere in your workbook. Within the event sub add code to update the value of your hard-coded/static cell with the value in your formula.

The Workbook_SheetChange sub must be in your workbook module (the default module name is "ThisWorkbook").

Here's an example of how you could use it. Just update the sheet names and range addresses for the variables myStaticCell and myFormulaCell.

In your ThisWorkbook Module:

Option Explicit

Public myVar As Variant ' stores last known value of static cell

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    ' Define the locations of your formula cell and static cell.
    Dim myStaticCell As Range
    Dim myFormulaCell As Range
    Set myStaticCell = Worksheets("Sheet2").Range("A2")
    Set myFormulaCell = Worksheets("Sheet1").Range("A2")

    ' Ignore changes made directly to the static cell by comparing
    '   the sheet name and address where the workbook change
    '   occurred (variables Sh and Target which are built into the
    '   Workbook_SheetChange event) with the sheet name and address
    '   of your static cell.
    If Sh.Name = myStaticCell.Parent.Name And _
            Target.Address = myStaticCell.Address Then Exit Sub

    ' Save the value to a public variable so it can
    '   be checked the next time something changes.
    myVar = myFormulaCell.Value

    ' If different, update the static cell with the
    '   value from your formula.
    If myStaticCell <> myVar Then myStaticCell = myFormulaCell.Value
End Sub

Upvotes: 0

Related Questions