• The forum (and all other resources in Chandoo.org) will be under maintenance and will be unavailable for a maximum of two hours on the first week of October 2023. The exact date, time and duration of the maintenance are subject to change. We regret the inconvience it may cause.
  • 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:
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.​
 
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
 
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!!!!
 
Back
Top