Is there a way to define and use a single cell?-CodePudding

I'm trying to single out a single cell, not a constant as it depends on the data at hand.

Dim lastrow As Integer
Dim sumcount As Integer

    'find last non-empty row in column C
     lastrow = Cells(Rows.Count, 3).End(xlUp).Row

    'add up the int values in the range in C
     sumcount = Application.WorksheetFunction.Sum(Range("C17", Cells(lastrow, 3)))
    
    'result goes into the next empty cell in C
     Cells(lastrow   1, 3).Value = sumcount

Now I'm trying to throw the cell Cells(lastrow 1, 3) into a variable so I can offset it and assign that offset cell a string value.

As far as I know that's only possible with the Range object, but I can't figure out the syntax to achieve that. Is there a property or method I'm not aware of?

CodePudding user response：

Cells returns a range object. So you should be able to simply do:

set myrange = cells(lastrow   1, 3)
myrange.value = "string"

CodePudding user response：

Add Column Total and Write to Cell Adjacent To the Right

Option Explicit

Sub Test()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim sfCell As Range: Set sfCell = ws.Range("C17")
    Dim slCell As Range: Set slCell = ws.Cells(ws.Rows.Count, "C").End(xlUp)
    
    Dim srg As Range: Set srg = ws.Range(sfCell, slCell)
    Dim sSum As Double: sSum = Application.Sum(srg)
    
    Dim dCell As Range: Set dCell = slCell.Offset(1)
    dCell.Value = sSum
    
    dCell.Offset(, 1) = "some string" ' adjacent to the right
    
End Sub