Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to get cell address next to current address
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Old address information updated to current addressi | Excel Worksheet Functions | |||
Get Address of Current Cell | Excel Programming | |||
Address of cell invoking current UDF call? | Excel Programming | |||
use current cell address as beginning of macro | Excel Discussion (Misc queries) | |||
How do I programmatically know the current cell's address | Excel Programming |