Need to get cell address next to current address

[jparnold]

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

 

[Mike]

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

 

[Rick Rothstein]

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

 

[jparnold]

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)























- Show quoted text -

Great Perfect!!! Thanks Jamie