CODEWITHSUNDEEP
vbaexcel
Pineapple
Pineapple

Reputation: 91

Running VBA code in alternate sheet triggers wrong results - despite referencing?

The below code seeks to pull the value from a cell in the the 'Input' sheet, and then display it in the 'Output' sheet. It then shows the difference between the last value recorded and expresses the figure as a percentage.

When I run this code with the Output sheet active it works. However, when I run it from the output sheet it doesn't. Instead, it displays the value I wish to copy in column F in the input sheet and displays the difference and percentage difference in the wrong cells in the Output sheet.

It looks correctly referenced to me, but it obviously isn't. Thoughts on how to correct?

I appreciate that the code could be tidier - i'm very new to this.

Sub Button1_Click()

Dim LastRow As Long
Dim RecentRow As Long

    With Sheets("Output")

         LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
         RecentRow = .Cells(.Rows.Count, "F").End(xlUp).Offset(1, 0).Row

         Range("F" & LastRow).Select
         ActiveCell.Offset(1, 0).Formula = "=Input!B4"
         ActiveCell.Offset(1, 0).Copy
         ActiveCell.Offset(1, 0).PasteSpecial (xlValues)

    End With

       ActiveCell.Offset(0, 1).Formula = "=(F" & RecentRow & "-F" & LastRow & ")"
       ActiveCell.Offset(0, 2).Formula = "=((F" & RecentRow & "/F" & LastRow & ")-1)"

End Sub

Thanks.

Upvotes: 1

Views: 72

Answers (1)

Nick Peranzi
Nick Peranzi

Reputation: 1375

The below code should fix your issue - it's because your Range("F" & LastRow).Select did not have a period before Range.

Sub Button1_Click()

Dim LastRow As Long
Dim RecentRow As Long

    With Sheets("Output")

         LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
         RecentRow = .Cells(.Rows.Count, "F").End(xlUp).Offset(1, 0).Row

         With .Range("F" & LastRow)
             .Offset(1, 0).Formula = "=Input!B4"
             .Offset(1, 0).Copy
             .Offset(1, 0).PasteSpecial (xlValues)
             .Offset(0, 1).Formula = "=(F" & RecentRow & "-F" & LastRow & ")"
             .Offset(0, 2).Formula = "=((F" & RecentRow & "/F" & LastRow & ")-1)"
         End With

    End With

End Sub

Furthermore, you can gain a bit more efficiency in your code with the below:

Sub Button1_Click()

Dim LastRow As Long

    With ThisWorkbook.Sheets("Output") 'Allow for code to work even if in another workbook.

         LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row

         With .Range("F" & LastRow)
             .Offset(1, 0).Value2 = ThisWorkbook.Sheets("Input").Range("B4").Value2
             .Offset(0, 1).Formula = "=(F" & LastRow + 1 & "-F" & LastRow & ")"
             .Offset(0, 2).Formula = "=((F" & LastRow + 1 & "/F" & LastRow & ")-1)"
         End With

    End With

End Sub

Upvotes: 1

Related Questions