Convert formulas to values based on status of another cell

[EssKayKay]

Hello,
I’ve asked a similar question before but I am still stumped (nothing new). I’m trying to find a way to convert formulas to values in a range based on the status of a different cell in the same row.

As example –
If M33 > 0 (date) then convert the formulas in Q33:S33 to values. This same condition applies to all the following rows - let’s say up to row 2000 (i,e,. range M33:M2000). All cells in column M will be populated in order (i.e., no vacant cells between dates). So, let’s say the last cell with a date is M77 (i.e., M78:M2000 are empty/blank). Then, convert cells in range Q33:S77 to values.

There is much more happening here but if I can get this working I’m hoping I’ll be able to proceed.

Thanks for viewing,
Steve K.

 

[kvsrinivasamurthy]

Try this macro.

Sub ConvertToValues()
Dim Lr&, SLr

Lr = Range("M" & Rows.Count).End(xlUp).Row
Range("Q33:S" & Lr).vlaue = Range("Q33:S" & Lr).vlaue
End Sub

 

[EssKayKay]

Try this macro.

Sub ConvertToValues()
Dim Lr&, SLr

Lr = Range("M" & Rows.Count).End(xlUp).Row
Range("Q33:S" & Lr).vlaue = Range("Q33:S" & Lr).vlaue
End Sub

Thanks kvsrinivasamurthy for your quick response. I tried running the code. However I received a Run-time error '438. Following are a couple screen shots showing what I am seeing. Any other suggestions would be appreciated.

Thanks again,
Steve K.

 

[kvsrinivasamurthy]

Sorry. Spelling mistake was there.
Sub ConvertToValues()
Dim Lr&, SLr

Lr = Range("M" & Rows.Count).End(xlUp).Row
Range("Q33:S" & Lr).Value = Range("Q33:S" & Lr).Value
End Sub

 

[EssKayKay]

Disregard my last post. I got it working. There was a typo in the code (vlaue > value). It now works perfectly.
Thank you very much kvsrinivasamurthy. It's so clean and works as a charm.

I have another location in my code that does something very similarly. I would like to explain it to you. Your understanding and resolution are so promising.
If you don't mind I'd like you to take a look at it.

Again, much appreciated,
Steve K.

EDIT -- I thought I posted this yesterday but forgot to hit POST.
I must say once again, my sincere thanks. . .

 

[kvsrinivasamurthy]

Thanks for feed back. Happy that your problem solved.