Conditionally converting forumlae to values

[darcus]

Hi

I hope you can help.

I can't get my head around how to do this.

Column T I enter a date value.
Once I enter a value, formulae in columns S and V calculate a figure.
This all happens on the same row.

I would like to run a command to convert every cell in columns S and V from their formulae to their values provided the cell in the same row in column T has a value entered into it. I am doing this in a large workbook so in effect, it is simply going to remove all formulae that have given a result only.

Is this doable?

Kind regards,
d

 

[FormR]

Hi, maybe you could give this a try:

Sub M()
Dim C As Range
For Each C In Range("T2:T" & Range("T" & Rows.Count).End(xlUp).Row).Cells
    If Len(C.Value) Then
        Range("S" & C.Row).Value = Range("S" & C.Row).Value
        Range("V" & C.Row).Value = Range("V" & C.Row).Value
    End If
Next C
End Sub

 

[darcus]

Thank You!!!

Hi, maybe you could give this a try:

Sub M()
Dim C As Range
For Each C In Range("T2:T" & Range("T" & Rows.Count).End(xlUp).Row).Cells
    If Len(C.Value) Then
        Range("S" & C.Row).Value = Range("S" & C.Row).Value
        Range("V" & C.Row).Value = Range("V" & C.Row).Value
    End If
Next C
End Sub

That worked brilliantly. It is very slow on a 20k+ row sheet. But it did the job.

Just so that I understand the code:
The beginning of the For statement determines when to finish the loop
If Len(C.Value) means that if the cell in C has a value then do whats between the If and the End If.

is that right?

d

 

[FormR]

is that right?

Hi, nearly, the For Loop line could be described as - work out the last used row column T and use that to define a range from T2 to the last used row in T and then cycle througheach cell within that range:

For Each C In Range("T2:T" & Range("T" & Rows.Count).End(xlUp).Row).Cells

 

[Rick Rothstein]

Once I enter a value, formulae in columns S and V calculate a figure.

If by "figure" you mean a number, then this macro might prove to be faster...

Sub ChangeFormulasDisplayingNumbersToContants()
  Dim Ar As Range
  With Range("S:S,V:V").SpecialCells(xlFormulas, xlNumbers)
    .Value = .Value
  End With
End Sub