Excel // VBA // Code need to convert formula results to the results after a certain date

Evremonde

New Member
Joined
Sep 29, 2016
Messages
25
Hello All:

I have a spreadsheet that performs countif functions in cells D4 to D46 (data on another tab). Cell D1 contains a date. I need some VBA code that will convert the countif results (cells D4 to D46) to values and to do this on the date contained in cell D1. I need to perform this same routine in cells E4 to E46. Cell E1 contains the date. Again, I need to perform this same routine for the same cell patterns through column S. Can you assist...?
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
I'm not 100% sure what you want, but this will convert ALL your mentioned ranges to values:
Code:
Sub ConvertToValues()
Range("D1:S1").Value = Range("D1:S1").Value
Range("D4:S46").Value = Range("D4:S46").Value
End Sub
Do you want the date ranges to be converted to values or are they some sort of criteria for which columns should be converted? Please elaborate...
 
Upvote 0
Can you please post some input as well as expected output? Converting countif results to what? And do "what" to date contained in "D1" ?
 
Upvote 0
The conversion from formula's (in the ranges specified) to values needs to occur on the date contained in cells D1 i.e the formulas in D4 to D46 converts from the formula's to the values on the date specified in D1. Does this help...?
 
Upvote 0
Would really appreciate if you can paste some input and output data to understand the question properly.
 
Upvote 0
Hello All:

I have a spreadsheet that performs countif functions in cells D4 to D46 (data on another tab). Cell D1 contains a date. I need some VBA code that will convert the countif results (cells D4 to D46) to values and to do this on the date contained in cell D1. I need to perform this same routine in cells E4 to E46. Cell E1 contains the date. Again, I need to perform this same routine for the same cell patterns through column S. Can you assist...?

The simplest answer is this:
Code:
Range("D4:S46").Copy Range("D4:S46")

as it will copy just the data and paste it all back as values and lose the formulae
 
Upvote 0
The conversion from formula's (in the ranges specified) to values needs to occur on the date contained in cells D1 i.e the formulas in D4 to D46 converts from the formula's to the values on the date specified in D1. Does this help...?
I think I understand just a little better. Not superclear though. Try this:
Code:
Sub ConvertToValues()
Dim iDate As Date, DateRng As Range, DateCell As Range, DateClm As Long, ValRng As Range
iDate = Date
With Worksheets("Sheet1") [COLOR=#008000]'Change "Sheet1" to match the name of the sheet/tab your "changeable" data is on.[/COLOR]
    Set DateRng = .Range("D1:S1")
    For Each DateCell In DateRng
        If DateCell.Value = iDate Then
            DateClm = DateCell.Column
            Set ValRng = .Range(.Cells(4, DateClm), .Cells(46, DateClm))
            ValRng.Value = ValRng.Value
        End If
    Next DateCell
End With
End Sub
What this code does:
It checks each cell in the range D1:S1 and looks for todays date (the date the code is run).
If theres a match, change the range 4:46 in corresponding column to values aka. removing formulas, if you will.
For example if cell G1 has todays date, then the formulas in G4:G46 will be converted to the values currently in G4:G46.

Is this what you are after?
 
Upvote 0
This appears to work well with one exception. The dates in cells D1...S1 result from formulas (I did not indicate this prior). When I run the macro, the formulas in D4 to D46 do not convert to values unless I replace the formulas in D1...S1 to the actual date values. If I place actual date values in the cells, the formulas convert as they should. Once I am past this, I have what I need. Any ideas? Thank you again for your assistance...
 
Upvote 0
How are the dates formatted at the source? I could guess that they are formatted as text?
You could try to convert the date in your formula to actual Excel date format:
Code:
=DATEVALUE(Your formula here)
or do it from the code:
Code:
Sub ConvertToValues()
Dim iDate As Date, DateRng As Range, DateCell As Range, DateClm As Long, ValRng As Range
iDate = Date
With Worksheets("Sheet1") 'Change "Sheet1" to match the name of the sheet/tab your "changeable" data is on.
    Set DateRng = .Range("D1:S1")
    For Each DateCell In DateRng
        If [COLOR=#ff0000]CDate([/COLOR]DateCell.Value[COLOR=#ff0000])[/COLOR] = iDate Then
            DateClm = DateCell.Column
            Set ValRng = .Range(.Cells(4, DateClm), .Cells(46, DateClm))
            ValRng.Value = ValRng.Value
        End If
    Next DateCell
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

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