Hi,

I'm working with a two column table (B5-C24). I am finding the cell
address of the cell with the last non-zero value in column C5-C24.
Based on that I need to get the cell address of the cell next to it,
i.e., if the first non-zero value cell in column C is C14, then I need
to get the address of cell B14 as well. My code is below.

There are four values I need to get as input to a new data source
chart range: StartTotalsAddress, EndTotalsAddress, (which I get) and
StartCategoryAddress, and EndCategoryAddress which will be from column
"B" and are adjacent to the cells for the varibles StartTotalsAddress
and EndTotalsAddress.

The varibles "Dim CategoryRange As Range", and "Dim CategoryCell As
Range" will be the counterparts to "TotalsRange" and "TotalsCell". I
hope that I am not confusing.


Thanks for your help!



Sub CreateNewSortRange()

Dim TotalsRange As Range
Dim TotalsCell As Range

Dim CategoryRange As Range
Dim CategoryCell As Range

Dim StartTotalsAddress As String
Dim EndTotalsAddress As String

Dim StartCategoryAddress As String
Dim EndCategoryAddress As String


Set TotalsRange = Sheets("TestRange").Range("C5:C24")
For Each TotalsCell In TotalsRange

If TotalsCell.Value 0 And StartTotalsAddress = "" Then

StartTotalsAddress = TotalsCell.Address

End If

If TotalsCell.Value = 0 Then

EndTotalsAddress = TotalsCell.Offset(-1).Address



Exit For
End If
Next


MsgBox "Totals Range Start= " & StartTotalsAddress
MsgBox "Totals Range End= " & EndTotalsAddress
'MsgBox "Category Range Start= " & StartCategoryAddress
'MsgBox "Category Range End= " & EndCategoryAddress