Convert formulas to values based on status of another cell

EssKayKay

Active Member
Joined
Jan 5, 2003
Messages
402
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Try this macro.
VBA Code:
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
 
Upvote 0
Try this macro.
VBA Code:
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.

1722283495720.png


1722283524075.png


Thanks again,
Steve K.
 
Upvote 0
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
 
Upvote 0
Solution
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. . .
 
Upvote 0

Forum statistics

Threads
1,223,375
Messages
6,171,734
Members
452,419
Latest member
mapa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top