Creating a range name based on cell content not being a negative value.

[burl_h]

The following code creates a range name called Cost1 on column F, the
range obviously starts at F2 with it's ending point defined as the last
cell in column F that is not a negative value.
The data in column F is sorted such that all the negative values are at
the bottom (sorted in ascending order) and all values greater than zero
are above the negative values, the positive values are sorted in
ascending order.

Example data
12
56
78
97
101
(59)
(53)
(34)

So long as the data is presented per the above sample the macro works
fine, the named range starts at F2 and ends at the last positive value.

I have 2 questions:-
1. how would you re-write the code assuming that column F is sorted in
desending order.
example:
101
97
78
56
12
(34)
(53)
(59)

2. how would you re-write the code assumng that column F is sorted in
desending order, but then the positive values in column F are in some
random order based on a sort to a different column.
example:
78
12
101
97
56
(34)
(53)
(59)
Sub Tester()

Dim Start As Range
Dim cell As Range
Dim vVal As Variant
Set Start = Range("f2")
vVal = Start.Value
For Each cell In Range(Cells(1, 6), Cells(1, 6).End(xlDown)(2))
If cell.Value < vVal Then
Range(Start, cell.Offset(-1, 0)).Name = "Cost1"
Set Start = cell
vVal = Start.Value
End If
Next

End Sub

burl_h

 

[Gary Keramidas]

maybe something like this, as long as negative numbers are always below the
positive ones.

Sub tester()

Dim cell As Range
Dim lastrow As Long, rng As Range
lastrow = Cells(Rows.Count, "F").End(xlUp).Row
Set rng = Range("F2:F" & lastrow)

For Each cell In rng
If cell.Value < 0 Then
Range("F2:F" & cell.Row).Offset(-1, 0).Name = "Cost1"
Exit Sub
End If
Next

End Sub

 

[Gary Keramidas]

forgot to mention that you should qualify the ranges with the worksheet name,
too.

 

[burl_h]

Gary,

The solution works fine, however the range name now starts at F1, it
should really start at F2, any suggestions?

burl_h

 

[burl_h]

Gary,

The solution works fine, however the range name now starts at F1, it
should really start at F2, any suggestions?

burl_h

 

[Gary Keramidas]

try this

Sub tester()

Dim cell As Range
Dim lastrow As Long, rng As Range
lastrow = Cells(Rows.Count, "F").End(xlUp).Row
Set rng = Range("F2:F" & lastrow)

For Each cell In rng
If cell.Value < 0 Then
Range("F2:F" & cell.Row - 1).Name = "Cost1"
Exit Sub
End If
Next

End Sub