• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get Value only

daxeman

New Member
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 <<<
Code:
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
 
Last edited by a moderator:

Marc L

Excel Ninja
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

Well-Known Member
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:
Code:
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

New Member
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:
Code:
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!!!!
 
Top