VBA to create range name based on value of cells in the range?

[EssKayKay]

Hello,
I’m still working on a way to speed up recalcs of my worksheet. My current concern is creating a named range that varies depending on other data.

I have an IF formula in column P that the results change based on the status of a cell in the corresponding row in column M. The formula is copied down column P as many as 2000 rows (albeit not likely that many).

Here is a test sample of the formula in Column P:
=IF(B33="","Cat",IF(M33<=0,C33,"Dog"))

What I’m looking for is a routine to create a range name (LookupDate) that is based on the cells in column P that contain values (dates). Following is a sample screen shot of what I’m looking at. In the screen shot, I would want the LookupDate range to be the cells in column P that have a date (i.e., “P37:P43”). The range changes every time an entry is made in column M; therefore the routine would have to be run multiple times. I believe I know how/where this will be triggered.

My concern is, as time progresses more entries will be made in column M which I assume may cause the “recalc” to drastically slow down. I’m hoping I can find a way to limit the times the routine will have to be called.

Any suggestions would be appreciated and thanks for viewing,
Steve K.

 

[SeniorNewbie]

with VBA you can accellerate the calculation by using arrays und the Select Case function. To set a range by cell value this litte examle may help:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True

If Target.Row = 1 And Target <> "" Then
    test Target.Column
End If

End Sub


Sub test(iCol As Integer)
Dim rng As Range, r As Integer, c As Integer

Cells.ClearFormats
Set rng = Range(Cells(1, iCol), Cells(10, iCol))
rng.Interior.Color = RGB(200, 100, 200)

End Sub

Code to a sheet

 

[EssKayKay]

with VBA you can accellerate the calculation by using arrays und the Select Case function. To set a range by cell value this litte examle may help:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True

If Target.Row = 1 And Target <> "" Then
    test Target.Column
End If

End Sub


Sub test(iCol As Integer)
Dim rng As Range, r As Integer, c As Integer

Cells.ClearFormats
Set rng = Range(Cells(1, iCol), Cells(10, iCol))
rng.Interior.Color = RGB(200, 100, 200)

End Sub

Code to a sheet

Thank you SeniorNewbie for your suggestion.
I'm a bit confused as to how this should be modified. I am quite interested in how your routine may "accelerate" the process. However, I think I found a solution to my problem. I'm going to keep working on it for now. If I get lost (which is quite often), I'm sure I will be back. I'll keep this in mind once I get my other problem resolved.

Again, much appreciated,
Steve K.

 

[SeniorNewbie]

Hi Steve,
the code above accellerates nothing, but

accellerate the calculation by using arrays und the Select Case function.

grx