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

[Evremonde]

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...?

 

[BQardi]

I'm not 100% sure what you want, but this will convert ALL your mentioned ranges to values:

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...

 

[Meesam_ali]

Can you please post some input as well as expected output? Converting countif results to what? And do "what" to date contained in "D1" ?

 

[Evremonde]

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...?

 

[Meesam_ali]

Would really appreciate if you can paste some input and output data to understand the question properly.

 

[Rhodie72]

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:

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

 

[BQardi]

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:

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?

 

[Evremonde]

I believe you have described my needs well. I will implement and let you know. Thank you.... TTYS

 

[Evremonde]

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...

 

[BQardi]

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:

=DATEVALUE(Your formula here)

or do it from the 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