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
--
Gary
"burl_h" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 wrote:
>> forgot to mention that you should qualify the ranges with the worksheet name,
>> too.
>>
>> --
>>
>>
>> Gary
>>
>>
>> "burl_h" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
>> >
>
|
Similar Threads
