VBA help

[AussieVic]

Hi, how can i get the below code to convert formulas to values?

With Sh
    i = .Range("N65536").End(xlUp).Row
    'Debug.Print i
    theformula = "=IF(ISNUMBER(MATCH(RC[-5],[Rec_Update.xls]Asset!C4,0)),"""",""CLEARED"")"
    
    For Each c In .Range("S8:S" & i)
        'Debug.Print c.Row
        cellValue = Val(CStr(c.Offset(0, -5).Value))
        If cellValue > 0 Then
            c.FormulaArray = theformula
            Application.DisplayAlerts = False
        End If
    Next c
    
End With

 

[Dazzawm]

Hi, how can i get the below code to convert formulas to values?

With Sh
    i = .Range("N65536").End(xlUp).Row
    'Debug.Print i
    theformula = "=IF(ISNUMBER(MATCH(RC[-5],[Rec_Update.xls]Asset!C4,0)),"""",""CLEARED"")"
 
    For Each c In .Range("S8:S" & i)
        'Debug.Print c.Row
        cellValue = Val(CStr(c.Offset(0, -5).Value))
        If cellValue > 0 Then
            c.FormulaArray = theformula
            Application.DisplayAlerts = False
        End If
    Next c
 
End With

Can you record copy>paste special>values and insert it into the code, something like this:-

Columns("N:N").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[mirabeau]

With Sh
    i = .Range("N65536").End(xlUp).Row
    'Debug.Print i
    theformula = "=IF(ISNUMBER(MATCH(RC[-5],[Rec_Update.xls]Asset!C4,0)),"""",""CLEARED"")"
    
    For Each c In .Range("S8:S" & i)
        'Debug.Print c.Row
        cellValue = Val(CStr(c.Offset(0, -5).Value))
        If cellValue > 0 Then
            c.FormulaArray = theformula
            c.Value = c.Value
            Application.DisplayAlerts = False
        End If
    Next c

End With

 

[Dazzawm]

Out of interest mirabeau, I am still learning also can what I suggested be done also?

 

[mirabeau]

Out of interest mirabeau, I am still learning also can what I suggested be done also?

Hi dazwm,

I guess we can all learn something from the posts on this forum.

My post wasn't because there was anything wrong with your suggestion, but just to indicate another approach.

Your approach should work OK to answer the question as asked.

I can make a couple of comments that may be of interest to you.
1. Usually it's not necessary or desirable use selections in VBA code.
Your code could be

With Columns("N:N")
    .Copy
    .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End With

or equivalently, if you want to reduce that by a couple lines

    Columns("N:N").Copy
    Columns("N:N").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

or just Range("N1") would do for the paste procedure since only the leading cell is needed.

2. Copying and pasting involves more typing and more operations, which may slow the code if it's being done a lot, say like 100k times in the same code.

3. Copying replaces whatever is on the clipboard, and this may be something that was useful otherwise, say to use in another part of the code where it may be more efficiently used.

 

[AussieVic]

Thanks

With Sh
    i = .Range("N65536").End(xlUp).Row
    'Debug.Print i
    theformula = "=IF(ISNUMBER(MATCH(RC[-5],[Rec_Update.xls]Asset!C4,0)),"""",""CLEARED"")"
    
    For Each c In .Range("S8:S" & i)
        'Debug.Print c.Row
        cellValue = Val(CStr(c.Offset(0, -5).Value))
        If cellValue > 0 Then
            c.FormulaArray = theformula
            c.Value = c.Value
            Application.DisplayAlerts = False
        End If
    Next c

End With