CODEWITHSUNDEEP
excelvba
mongoose00318
mongoose00318

Reputation: 131

Using Range.Address to specify the high end of the range

If I'm getting the result $F$1:$F$21 from using .Address on a Range, is there a way to seperate just the F21 part and then use .Offset to tell excel the cell I want to put a value in?

Public Sub TotalAudits()
    With Worksheets(1)
        'Dim lastrow As Long: Set lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Dim celTotal As Range: Set celTotal = .Cells(.Rows.Count, "A").End(xlUp).Row.Offset(2, 4)
        celTotal.Value = "Monthly Totals"
        With celTotal
            .Font.Bold = True
            .Font.Italic = True
            .HorizontalAlignment = xlRight
        End With

        Dim rngMonth As Range
        For Each rngMonth In Range("F1:Q" & .Cells(.Rows.Count, 1).End(xlUp).Row).Columns
            Dim colTotal As Integer: colTotal = rngMonth.Cells.SpecialCells(xlCellTypeConstants).Count - 1
            .Range(Split(rngMonth.Address(False, False), ":")(1)).Offset(2, 0).Value = colTotal
            'MsgBox (rngMonth.Address)
        Next rngMonth
        'MsgBox (lastrow)
    End With
End Sub

Specifically, this area of code....

MsgBox (rngMonth.Address)

I want to offset the last part of the address by 2 rows and insert the value stored in colTotal in that offset cell.

Upvotes: 0

Views: 63

Answers (2)

Mikku
Mikku

Reputation: 6654

As BigBen Said you should change the way you are calculating the LastRow, try something like this:

lastrow = .Cells(.Rows.Count, "A").End(xlUp).row

Just to get F21 out of rngMonth.Address, use

Replace(split(rngMonth.Address,":")(1),"$","")

Or:

Split(rngMonth.Address(False, False), ":")(1)

Final Line you would want to use is:

Range(Split(rngMonth.Address(False, False), ":")(1)).Offset(2, 0).Value = colTotal

Complete Code:

Public Sub TotalAudits()

    With Worksheets(1)
        Dim lastrow As Long: lastrow = .Cells(.Rows.Count, "A").End(xlUp).row
        Dim celTotal As Range: Set celTotal = .Range("A" & lastrow).Offset(2, 4)
        With celTotal
            .Value = "Monthly Totals"
            .Font.Bold = True
            .Font.Italic = True
            .HorizontalAlignment = xlRight
        End With

        Dim rngMonth As Range
        For Each rngMonth In Range("F1:Q" & .Cells(.Rows.Count, 1).End(xlUp).row).Columns
            Dim colTotal As Integer: colTotal = rngMonth.Cells.SpecialCells(xlCellTypeConstants).Count - 1

            MsgBox (rngMonth.Address)
            Range(Split(rngMonth.Address(False, False), ":")(1)).Offset(2, 0).Value = colTotal

        Next rngMonth
        MsgBox (lastrow)
    End With
End Sub

Upvotes: 1

Tom
Tom

Reputation: 9878

You can get the last cell of a range using the following

Dim rngMonth As Range
Set rngMonth = Range("F1:F21")

MsgBox rngMonth.Cells(rngMonth.Cells.Count).Offset(2, 0).Address

This selects the last cell in a range, offsets it be two rows and returns the address

Upvotes: 1

Related Questions