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

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

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

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