Add 1 to the cell above in another sheet

Question

I am trying to run a macro that: Adds 1 to the cell above IN another sheet in the workbook and places it in the cell below. However the cell that it adds to is dynamic, the macro adds a new line for a new entry. Below is the code I have so far. cellRef should do the adding, but I am getting random figures and errors! (mismatch once and random figures other times)

Sub update_Number()

cellRef = ActiveWorkbook.Sheets("RMA").Range("A" & (ActiveCell.Row)).Offset(-1, 0).Value

ActiveWorkbook.ActiveSheet.Select
customeRef = Range("c" & (ActiveCell.Row))
customerName = Range("d" & (ActiveCell.Row))
customerCountry = Range("e" & (ActiveCell.Row))
customerCompany = Range("f" & (ActiveCell.Row))
datePaid = Range("g" & (ActiveCell.Row))

Dim wks As Worksheet
Set wks = Sheets("RMA")

With wks

Dim RowCount As Long
RowCount = .Range("A8").End(xlDown).Row + 1

.Cells(RowCount, 1) = cellRef + 1
.Cells(RowCount, 2) = customeRef
.Cells(RowCount, 3) = customerName
.Cells(RowCount, 4) = customerCountry
.Cells(RowCount, 5) = customerCompany
.Cells(RowCount, 6) = datePaid


End With

End Sub

[This](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) will get you started... — Siddharth Rout, Sep 09 '15 at 11:07
The information on that sheet adds rows I need to use the value in the cells! — tbowden, Sep 09 '15 at 11:20
You are finding the row number incorrectly. Ok, Lets start from the beginning. You have two sheets say "Sheet1" and "RMA". Now explain what do you need to exactly do? — Siddharth Rout, Sep 09 '15 at 11:56
I am trying to add the next number in a list in column A, in the RMA Sheet using a macro. So if there is a 2 in cell A5 when I start the macro I would like 3 to show in A6 then if I begin the macro again a 4 should show in A7. But the macro uses some information from Sheet 1 and the macro button is on sheet 1 too, thats why its a bit complicated! — tbowden, Sep 09 '15 at 12:08
it is not complicated :) Let me post an answer.. quick question.. When you say `customeRef = Range("c" & (ActiveCell.Row))` Where are you picking this data from? `Sheet1` or `RMA`? — Siddharth Rout, Sep 09 '15 at 12:19

score 1 · Accepted Answer · answered Sep 09 '15 at 12:37

What you need to do is fully qualify your object. Once you do that you will not get random values. Excel will know which sheet, which cell you are referring to. See this example

Sub update_Number()
    Dim Sno As Long, LRow As Long, NewRow As Long
    Dim wsO As Worksheet, wsI As Worksheet
    Dim customeRef, customerName, customerCountry
    Dim customerCompany, datePaid

    Set wsO = ThisWorkbook.Sheets("RMA")
    Set wsI = ThisWorkbook.Sheets("Sheet1")

    '~~> Get values from Sheet1
    With wsI
        customeRef = .Range("c" & (ActiveCell.Row))
        customerName = .Range("d" & (ActiveCell.Row))
        customerCountry = .Range("e" & (ActiveCell.Row))
        customerCompany = .Range("f" & (ActiveCell.Row))
        datePaid = .Range("g" & (ActiveCell.Row))
    End With

    '~~> Work with RMA Sheet
    With wsO
        '~~> Get the last Row
        LRow = .Range("A" & .Rows.Count).End(xlUp).Row
        '~~> Increment the number
        Sno = .Range("A" & LRow).Value + 1
        '~~> New row where we need to write
        NewRow = LRow + 1

        .Cells(NewRow, 1) = Sno
        .Cells(NewRow, 2) = customeRef
        .Cells(NewRow, 3) = customerName
        .Cells(NewRow, 4) = customerCountry
        .Cells(NewRow, 5) = customerCompany
        .Cells(NewRow, 6) = datePaid
    End With
End Sub

Add 1 to the cell above in another sheet

1 Answers1