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.

 

[EssKayKay]

Thank you Alex. Adding the Application.EnableEvents = False did help but I am still having the “hang” issue appearing on another sub. I will include the EnableEvents line but I still think/hope my problem would be easier (not necessarily better) resolved for me if there was a way to run the formulas to values conversion routine via a separate sub and not part of Sub Worksheet_Change.

So again without being too demanding here, is there some way for a subroutine that can be triggered by a command button that converts formulas to values based on the status of a second cell. That is, if a cell in Range(“T33:T133) > 0 (date) then convert the formula in the adjacent cell (column S) to a value.

As example - if T33 is populated, then convert the formula in S33 to a value. This same condition applies to all the remaining rows in the range (i.e., if T34 >0 convert S34 to value, if T35>0 convert S35 to value, and so on up to T133). All entries in the range will be consecutive (i.e., there will be no empty/blank cells between entries). There will be empty cells after the last entry. As time progresses more cells in column T will be populated (up to a max of T133) and thus require the formula/value conversion.

Steve K.

 

[EssKayKay]

OKAY - I've got this all running and I thank you all for your patience with me. With the help of 6StringJazzer here is my code to convert formulas to values:

Dim Row As Long
'           Look for non-blank cells in column T starting at row 33
   Row = 33
   Do Until Cells(Row, "T").Value = "" 
'           If the corresponding cell in columns S is a formula, convert to a value
      If Left(Cells(Row, "S").Formula, 1) = "=" Then
         Cells(Row, "S").Value = Cells(Row, "S").Value
      End If
      Row = Row + 1
   Loop

Thank you Jeff.

I still have a minor question on combining Count() into a Range(). I think I'm going to create a new thread for this if that's OK.
Steve K.

 

[6StringJazzer]

I think you would get a better response if you do go ahead and start a new thread, because it will have focus on that particular point. Just my opinion, not direction.