Get Value only

[daxeman]

Hi,

I am trying to obtain a value only by running the following code in VBA. Its taking a long time to run. Is there a code I can use to speed this process up?

>>> use code - tags <<<

Sub SMUHrs()

Dim last_row As Long

last_row = Cells(Rows.Count, 3).End(xlUp).Row

Range("B2").FormulaR1C1 = "=SUMIFS(SMUHrs!R6,SMUHrs!R1,digrates!RC[-1])"

Range("B2").AutoFill Destination:=Range("B2:B" & last_row)

Dim rngCell As Range

For Each rngCell In Selection
    If rngCell.HasFormula Then
        rngCell.Value = rngCell.Value
    End If
Next

End Sub

 

[Marc L]

Hi, maybe not using a formula as you don't keep it …​

You can also replace the loop just using the selection in order to allocate values instead of formulas at once.​

​

 

[p45cal]

Probably no need to test for the existence of a formula.
If, in
For Each rngCell In Selection
the Selection is the same as the just entered formulae then try:

Sub SMUHrs()
Dim last_row As Long
last_row = Cells(Rows.Count, 3).End(xlUp).Row
With Range("B2:B" & last_row)
  .FormulaR1C1 = "=SUMIFS(SMUHrs!R6,SMUHrs!R1,digrates!RC[-1])"
  .Value = .Value
End With
End Sub

 

[daxeman]

Probably no need to test for the existence of a formula.
If, in
For Each rngCell In Selection
the Selection is the same as the just entered formulae then try:

Sub SMUHrs()
Dim last_row As Long
last_row = Cells(Rows.Count, 3).End(xlUp).Row
With Range("B2:B" & last_row)
  .FormulaR1C1 = "=SUMIFS(SMUHrs!R6,SMUHrs!R1,digrates!RC[-1])"
  .Value = .Value
End With
End Sub

@p45cal this code works and is faster!!. Appreciate the help!!!!