VBA to create Range Name and convert formulas to values?

[EssKayKay]

Hello,
I have a couple questions on range names etc.

1. Create a range name –
I’m trying to create a range name based on the status of a cell in the range; however the range limits change. That is, if the Range(“T33:T40) contains a date, then create the range name (say TEST) for Range(R33:T40). The beginning cell in the range will always be row 33 – however, the last row in the range will change. All consecutive cells in column “T” will be populate (no blanks).

2. Convert formulas to values –
If a cell in column “T” > 0 (contains a date) then convert the corresponding formula in that row in column S to a value. That is, if Range(“T33:T40”) >0 then convert Range(S33:S40”) to values. Again however, the range changes. It will always start with T33 but the end limit will change.

I realize I may be coming a bit of a pest here with my constant questions but I am learning, so please bear with me.

Thanks for viewing,
Steve K.

 

[6StringJazzer]

A dynamic range name like the one you need can be done without VBA. The following range formula will give the range starting in R33:T33 and continuing down as many rows as there are dates in column T. This assumes there is no further data in column T after the last date.

=$R$33:$T$40X($T$33:$T$999,COUNT($T$33:$T$999))

where 999 is the highest possible row number where you could have data. This could be more flexible if I know what's in the first 32 rows.

The second one bugs me a little bit because I don't know when the dates in column T could change. Once you enter a date can it be deleted, or changed in such a way as to affect the formula in S? That is, can you change a date and invalidate the value that you froze in S? And how/when are dates entered?

The answer to #2 at face value is this code added to the sheet module for the sheet with the data. This will freeze the value in column S any time a date is added (or changed!) in column T.

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Cell As Range
  
   For Each Cell In Target
  
      If Cell.Column = [T1].Column And Cell.Row >= 33 And Cell > 0 Then
         Cells(Cell.Row, "S").Value = Cells(Cell.Row, "S").Value
      End If
  
   Next Cell

End Sub

 

[EssKayKay]

A dynamic range name like the one you need can be done without VBA. The following range formula will give the range starting in R33:T33 and continuing down as many rows as there are dates in column T. This assumes there is no further data in column T after the last date.

=$R$33:$T$40X($T$33:$T$999,COUNT($T$33:$T$999))

where 999 is the highest possible row number where you could have data. This could be more flexible if I know what's in the first 32 rows.

The second one bugs me a little bit because I don't know when the dates in column T could change. Once you enter a date can it be deleted, or changed in such a way as to affect the formula in S? That is, can you change a date and invalidate the value that you froze in S? And how/when are dates entered?

The answer to #2 at face value is this code added to the sheet module for the sheet with the data. This will freeze the value in column S any time a date is added (or changed!) in column T.

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Cell As Range
 
   For Each Cell In Target
 
      If Cell.Column = [T1].Column And Cell.Row >= 33 And Cell > 0 Then
         Cells(Cell.Row, "S").Value = Cells(Cell.Row, "S").Value
      End If
 
   Next Cell

End Sub

Thank you Jeff for your quick response.

1. I’m confused on this one. I’m not sure where (or how) you place your formula.

I'll try to explain exactly what I have in mind but what I want is a range name to be used in a form with a dropdown list that displays results based on data in the range. As more dates are added (completion dates) more options become available in the dropdown list. Therefore, as dates are added, I would like a VBA (or something) to delete the existing range name (example TEST), then create a new range name (TEST) based on the added cell.

My trial code is something like this.

Range("T33").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(0, -2).Select

However, this does not set the entire Range(“R33:T##”). It only sets R33:R##. It does not hold the column T portion. If I could get a routine the create a range name (say TEST) for Range R33:T##, then I would first delete the old range named TEST and create a new one based on the new range limits and call it TEST.

2. Your Sub for #2 (converting formulas to values) appears to work perfectly. I have more testing (creating forms etc.) but it looks really promising.

Thanks again Jeff,
Steve K.

 

[EssKayKay]

I think I figured it out.

Range("R33:T" & Range("T27").Value + 32).Select

This is where cell T27 is

= Count($T$33:$T$999)

Thanks again - your suggestion put me in the right direction,
SKK

 

[EssKayKay]

Is there a way to modify the code

Range("R33:T" & Range("T27").Value + 32).Select

to include the Count function in the line rather than having to address it from another cell?
That is, remove the "T27" and replace it with something like (which I know isn't correct) :

Range("R33:T" & Range("Count($T$33:$T$999).value + 32")).Select

Steve

 

[6StringJazzer]

First of all there is a terrible error in my formula, that I think was some kind of paste error. It should be:

=$R$33:INDEX($T$33:$T$999,COUNT($T$33:$T$999))

then create the range name (say TEST) for Range(R33:T40)

what I want is a range name to be used in a form with a dropdown list

I'm unclear on what you're trying to do. A dropdown list can be defined only by a single column (or row) of data. Your range shows three columns.

You would take my formula and use it to define a name in the name manager:

 

[EssKayKay]

First of all there is a terrible error in my formula, that I think was some kind of paste error. It should be:

=$R$33:INDEX($T$33:$T$999,COUNT($T$33:$T$999))

I'm unclear on what you're trying to do. A dropdown list can be defined only by a single column (or row) of data. Your range shows three columns.

You would take my formula and use it to define a name in the name manager:
View attachment 114922

Thanks for getting back to me Jeff. I have been playing with your suggestions but still no luck. I must stress, I am not a programmer so this is all somewhat foreign to me. As I noted, I have it working using the “Count” function so I’m going to leave this as is. I just found out I have a more serious issue with this worksheet which I have to work on first.

Regarding my post #5 above (which is quite minor), rather than putting

= Count($T$33:$T$999)

In cell T27 then calling cell T27 in my line

Range("R33:T" & Range("T27").Value + 32).Select

Is there a way to hardcode the Count function into the line above? That is something like (which I know doesn’t work) –

Range("R33:T" & Range("Count($T$33:$T$999).value + 32")).Select

If this is getting to convoluted we can just move on since I do have this working. Like I said, I have a bigger problem which I have to fix first.

Thanks for your time and consideration,
Steve K.

 

[EssKayKay]

First of all there is a terrible error in my formula, that I think was some kind of paste error. It should be:

=$R$33:INDEX($T$33:$T$999,COUNT($T$33:$T$999))

I'm unclear on what you're trying to do. A dropdown list can be defined only by a single column (or row) of data. Your range shows three columns.

You would take my formula and use it to define a name in the name manager:
View attachment 114922

Hello Jeff

I just became aware of a problem I’m having with the code to change formulas to values :

Private Sub Worksheet_Change(ByVal Target As Range)
‘------ Converts formula to value when date is entered in column T ------
Dim Cell As Range
For Each Cell In Target
If Cell.Column = [T1].Column And Cell.Row >= 33 And Cell > 0 Then
Cells(Cell.Row, "S").Value = Cells(Cell.Row, "S").Value
End If
Next Cell
‘-------------------------------------------------------------------------------------------
End Sub

It affects two other subs that I run to help clear my worksheet when the user wishes to start anew.

Here are the two routines:

Sub MaxData()
' Sets up Maximum DATA range to 2000 payments
Application.ScreenUpdating = False
Rows("34:2033").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B2035:L2035").Select
Selection.Copy
Range("B2033").Select
Range(Selection, Selection.End(xlUp)).Select

Range("B2036:K2036").Select
Selection.Copy
Range("B2033").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

Sub DataRange()
' Defines Data range names (PmtNo, InterestDue, PrincipalPaid)
Application.ScreenUpdating = False

'-------- Set Number of Payments range (PmtNo) ----------
Lr = Cells(Rows.Count, "B").End(xlUp).Offset(-3, 0).Row
Range("B33").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="PmtNo", RefersToR1C1:="=Amortize!R33C2:R" & Lr & "C2"
ActiveWorkbook.Names("PmtNo").Comment = ""

Range("A4,H5").Select
Range("H5").Activate
End Sub

What I would like is a separate subroutine that I can run via another process (command button) rather than having the conversion process automatically via Private Sub Worksheet_Change. Therefore, is there a way to modify the code to be triggered via a command button? I'd like the sub to proceed down the range which will be rows 33:133 (i.e., 100 cells).

Sorry for muddying the water here but I just became aware of this. Hopefully you can help me once again.
I hope you don’t feel as I am being overly demanding or persistent here Jeff, it’s just that I am stumped.

Thanks again,
Steve

 

[EssKayKay]

OOPS - posted wrong sub above (that's why I wish we could edit longer but I understand the policy). Disregard Sub DataRange(). I should have posted"

Sub MinData()
' Reduces DATA range to only rows w/payments
Application.ScreenUpdating = False
UnProtect_It
   Application.ScreenUpdating = False
   Range(Range("LastDataRow").Value & ":" & Range("LastPmtRow").Value + 2).Select
   UnProtect_It
   Selection.Delete Shift:=xlUp
Protect_It
End Sub

Here's a little more info -
What happens is when the other two subs (MinData & MaxData) are called from a third sub called ClearALL(), everything hangs. I have numerous other processes in my Sub Worksheet_Change. As a test, I removed all except the conversion routine but the worksheet still hangs when I run ClearALL.

 

[Alex Blakenburg]

everything hangs.

Typically when you run subs like your ClearAll and it sub processes you don't want to keep calling the Worksheet Change Event.
Since ClearAll is the controlling Sub start it with Application.EnableEvents = False and end it with Application.EnableEvents = True.
If you think you will want to run Max & Min independently then you can do the same in those subs.

PS: Any reason Min is running unprotect twice ? It probably doesn't need to turn off ScreenUpdating twice either but I would need to see the unprotect sub to be sure.

I about to Log off for the night so hopefully Jeff will keep running with it.