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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to get cell address next to current address
"jparnold" wrote in message ... 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 You may want to look into the offset method? for example range("c5").Offset(0,-1).Address will return b5 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to get cell address next to current address
Try changing this part of your code...
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 to this... If TotalsCell.Value 0 And StartTotalsAddress = "" Then StartTotalsAddress = TotalsCell.Address StartCategoryAddress TotalsCell.Offset(0, -1).Address End If If TotalsCell.Value = 0 Then EndTotalsAddress = TotalsCell.Offset(-1).Address EndCategoryAddress TotalsCell.Offset(-1, -1).Address Exit For End If -- Rick (MVP - Excel) "jparnold" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to get cell address next to current address
On Dec 23, 11:39*am, "Rick Rothstein"
wrote: Try changing this part of your code... * * * 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 to this... * * * If TotalsCell.Value 0 And StartTotalsAddress = "" Then * * * * * StartTotalsAddress = TotalsCell.Address * * * * * StartCategoryAddress TotalsCell.Offset(0, -1).Address * * * End If * * * If TotalsCell.Value = 0 Then * * * * * EndTotalsAddress = TotalsCell.Offset(-1).Address * * * * * EndCategoryAddress TotalsCell.Offset(-1, -1).Address * * * * * Exit For * * * End If -- Rick (MVP - Excel) "jparnold" wrote in message ... 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- Hide quoted text - - Show quoted text - Great Perfect!!! Thanks Jamie |
Reply |
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 |