How can I set the value of a cell in Cells().Select command to the value of a cell that has a formula in it?

Question

I'm trying to make a macro that copies the values inside certain cells of sheet1 and pastes then in sheet2.

This is a formula that i wrote inside cell "AI2":

=IFERROR(SUM(1+AH:AH),"0")

and it produces a number that I want to use in the macro as a variable row coordinate.

This is the code i have in my worksheet in order to trigger the macro:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
     If Range("AI2") <> 0 Then
     Call macro1
     End If
End Sub

And this is the macro:

Sub macro1()
Dim RV As Integer
RV = Sheets("sheet1").Range("AI2").Value
Cells(RR, 33).Select
Range(ActiveCell.Offset(0, -6), ActiveCell.Offset(0, -1)).Select
Selection.Copy
Sheets("sheet2").Select
Range("A1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
End Sub

If I delete the first 3 lines of code, the macro works, but I have to manually select the cell for the offsets to reference from.

I need to make it so the value of cell "AI2" is used as the first coordinate in this line of code:

Cells(RR, 33).Select

I am very new to any kind of programming, but I want to learn this in order to achieve my goals for this spreadsheet and future ones with similar functions.

([Please don't use `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba)). Also, what is `Cells(RR, 33).Select`? Does that not throw an error? At the very least I'd think that needs to be `Cells(33,"RR")`? — BruceWayne, Feb 13 '19 at 19:40
'cells(RR, 33).select' RR would be the value of cell "AI2" which would match the row that has the info that needs copying, where the 33 would be the column (AG) — Nuno Filipe, Feb 13 '19 at 19:45
I corrected that but still, when I make the value of cell "AI2" change, even though it becomes unequal to "0", it does not trigger anything, but no error messages show — Nuno Filipe, Feb 13 '19 at 19:48
Worksheet_Change will not pickup a change due to a formula, only ones done manually. — Scott Craner, Feb 13 '19 at 19:56
To extend on @ScottCraner comment, you need to change your `Worksheet_Change` event to trigger when a manual change is made that you expect will make a change to `Range("AI2")`. So, it looks like you need your macro to look for changes in `Column AH` rather the entire sheet — urdearboy, Feb 13 '19 at 20:01
@urdearboy that would make the reference cell "AH5" and the range to copy "AB5:AG5" — Nuno Filipe, Feb 13 '19 at 20:12
I don't see why `AH5` is relevant here. If `AI2 = 5`, you want to copy `AB:AG` on 5th row? And if `AI2 = 10` you want to copy `AB:AG` on 10th row? If so, the solution I provided you will, *should*, work — urdearboy, Feb 13 '19 at 20:18
@Nuno Filipe: You cannot upvote, yet. But soon when you'll have 15 reputation you can come back and do so. Thanks for the kind reply. — VBasic2008, Feb 13 '19 at 21:13

urdearboy · Accepted Answer · 2019-02-13T20:52:36.060

1

I am limiting the scope of your Worksheet_Change to only fire when a change is registered in Column AH since this is the column that will trigger a formula change in Column AI

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 34 Then
        If Range("AI2") <> 0 Then
            Macro2
        End If
    End If
End Sub

Sub Macro2()

Dim cs As Worksheet: Set cs = ThisWorkbook.Sheets("Sheet1")
Dim ps As Worksheet: Set ps = ThisWorkbook.Sheets("Sheet2")

Dim xRow As Long, LR As Long
LR = ps.Range("A" & ps.Rows.Count).End(xlUp).Offset(1).Row
xRow = cs.Range("AI2").Value

cs.Range(cs.Cells(xRow, "AB"), cs.Cells(xRow, "AG")).Copy
    ps.Range("A" & LR).PasteSpecial xlPasteValues

End Sub

edited Feb 13 '19 at 20:52

answered Feb 13 '19 at 20:16

urdearboy

14,439
5
28
58

this worked to some extent, as it is always copying the top row within the (AB:AG) column range no matter the row within column 34. I will try to figure out how your code works as I don't know what half of those commands mean. Thank you :) – Nuno Filipe Feb 13 '19 at 20:34
@urdearboy I got it to work, the issue was that the `If Range("AI2") > 0...` Needed to be `If Range("AI2") <> 0...` Because the way "AI2" formula is written =IFERROR(SUM(1+AH:AH),"0") the cell value was 1+whatever value would be in the AH column, so i'm guessing the macro was always looking for the 1. when i changed the ">" to "<>" it worked perfectly :) Nonetheless I will still try to learn your code so i can eventually write working macros with my own knowledge Thank you very much for the help :D – Nuno Filipe Feb 13 '19 at 20:50
@VBasic2008 both of them worked and I thank you both, for the time and patience you expended with my post. I upvoted both solutions and accepted urdearboy's answer because his macro pasted the info in a new row every time it ran, making it so i keep all the info logged of any row i edit in collumn "AH" I hope that if we speak again, I will at least be able to understand the code Again, Thank you very much for all the help and my apologies for the eventual dead neuron. Cheers – Nuno Filipe Feb 13 '19 at 21:03

VBasic2008 · Answer 2 · 2019-02-13T23:12:26.777

Copy Range to First Empty Cell

Calculate

If you are using a formula in the cell range AI2 you should use the Worksheet Calculate event which will occur everytime the formula is being calculated.

Standard Module

Option Explicit

Public Const strRange As String = "AI2"
Public vntValue As Variant

Sub macro1()

    Dim rng As Range  ' Target Cell Range
    Dim RV As Long    ' Row Value

    ' In Target Worksheet
    With ThisWorkbook.Sheets("Sheet2")
        ' Calculate the first empty (unused) cell in column A (A1 not included).
        Set rng = .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
    End With

    ' In Source Worksheet
    With ThisWorkbook.Worksheets("Sheet1")
        ' Write the value of Row Cell to Row Value.
        RV = .Range(strRange).Value
        With .Cells(RV, "AH") ' or 33
            ' Copy range from "AB" to "AG" in row defined by Row Value in
            ' Source Worksheet to the range from "A" to "F" in row of Target
            ' Cell Range in Target Worksheet.
            rng.Resize(, 6) = Range(.Offset(0, -6), .Offset(0, -1)).Value
        End With
    End With

End Sub

Sheet1

Option Explicit

Private Sub Worksheet_Calculate()
    If vntValue <> Range(strRange).Value Then
        vntValue = Range(strRange).Value
        If Range(strRange).Value <> "0" Then macro1
    End If
End Sub

ThisWorkbook

Option Explicit

Private Sub Workbook_Open()
    vntValue = Worksheets("Sheet1").Range(strRange).Value
End Sub

Change

If you are manually changing the values in the cell range AI2, you have to use the Worksheet Change event.