VBA to convert formulas to value

[talkvinith]

Hi

Please help Urgent!!!!!


Could any one please help me in creating a VBA for converting formulas to value of any entire row if a certain criteria matches.




For example: In the attached sheet I want to covert the formulas of an entire row if the "Status" in D column in "Complete".


Thank you!!

[mancubus]

try with a copy of original file

Sub vbax_54491_Formulaz_To_Valz_On_Condition()

    Dim cll As Range
    
    With Worksheets("MySheet") 'change MySheet to suit
        .AutoFilterMode = False
        .Cells(1).AutoFilter Field:=4, Criteria1:="=Complete"
        For Each cll In .UsedRange.Offset(1).SpecialCells(12)
            If cll.HasFormula Then cll.Value = cll.Value
        Next
        .AutoFilterMode = False
    End With

End Sub

[talkvinith]

Can you help me to do this without auto filter?

[GTO]

I'm not sure what disadvantage there would be to using AutoFilter, but maybe this is more what you were thinking?

Option Explicit
  
Sub example()
Dim rngFound As Range
Dim rng2Search As Range
Dim lRowLookingAt As Long
  
  With Sheet1 '<--- Use whatever the real sheet's CodeName is
    Set rngFound = RangeFound(.Range("A:D"))
    If Not rngFound Is Nothing Then
      If Not rngFound.Row < 2 Then
        For lRowLookingAt = rngFound.Row To 2 Step -1
          If .Cells(lRowLookingAt, 4).Value = "Complete" Then
            .Cells(lRowLookingAt, 4).Offset(0, -2).Resize(, 2).Value = .Cells(lRowLookingAt, 4).Offset(0, -2).Resize(, 2).Value
          End If
        Next
      End If
    End If
  End With
  
End Sub
  
Function RangeFound(SearchRange As Range, _
                    Optional ByVal FindWhat As String = "*", _
                    Optional StartingAfter As Range, _
                    Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
                    Optional LookAtWholeOrPart As XlLookAt = xlPart, _
                    Optional SearchRowCol As XlSearchOrder = xlByRows, _
                    Optional SearchUpDn As XlSearchDirection = xlPrevious, _
                    Optional bMatchCase As Boolean = False) As Range
    
    If StartingAfter Is Nothing Then
        Set StartingAfter = SearchRange.Cells(1)
    End If
    
    Set RangeFound = SearchRange.Find(What:=FindWhat, _
                                      After:=StartingAfter, _
                                      LookIn:=LookAtTextOrFormula, _
                                      LookAt:=LookAtWholeOrPart, _
                                      SearchOrder:=SearchRowCol, _
                                      SearchDirection:=SearchUpDn, _
                                      MatchCase:=bMatchCase)
End Function

Hope that helps,

Mark

[mancubus]

Can you help me to do this without auto filter?

what is wrong with autofilter.

[snb]

I don't like 'Urgent'.
I don't like exclamation marks either.

Sub M_snb()
   sn = Cells(1).CurrentRegion

   For j = 1 To UBound(sn)
      If LCase(sn(j, 4)) = "complete" Then Cells(1).CurrentRegion.Rows(j) = Application.Index(sn, j)
   Next
End Sub

[talkvinith]

Thank all...!! GTO your coding is really working fine.

A final help if possible.

I want to remove the name in A column if the D column status is "Verification Completed" or Verification not Required" and can you able to add the coding to the previous macro itself.


Thanks Again!!

[GTO]

Here you go. I assumed we only want to do the second check if the first one passes. Change to suit....

Option Explicit
 
Sub example()
Dim rngFound As Range
Dim rng2Search As Range
Dim lRowLookingAt As Long
With Sheet1 '<--- Use whatever the real sheet's CodeName is
Set rngFound = RangeFound(.Range("A:E"))
If Not rngFound Is Nothing Then
If Not rngFound.Row < 2 Then
For lRowLookingAt = rngFound.Row To 2 Step -1
If .Cells(lRowLookingAt, 5).Value = "Complete" Then 
.Cells(lRowLookingAt, 5).Offset(0, -3).Resize(, 3).Value = .Cells(lRowLookingAt, 5).Offset(0, -3).Resize(, 3).Value
If (.Cells(lRowLookingAt, 4).Value = "Verification Completed" Or .Cells(lRowLookingAt, 4).Value = "Verification not Required") Then
.Cells(lRowLookingAt, 1).Value = vbNullString
End If 
End If
Next
End If
End If
End With
End Sub
 
Function RangeFound(SearchRange As Range, _
 Optional ByVal FindWhat As String = "*", _
 Optional StartingAfter As Range, _
Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
Optional LookAtWholeOrPart As XlLookAt = xlPart, _
Optional SearchRowCol As XlSearchOrder = xlByRows, _
Optional SearchUpDn As XlSearchDirection = xlPrevious, _
Optional bMatchCase As Boolean = False As Range
IF StartingAfter Is Nothing Then
Set StartingAfter = SearchRange.Cells(1)
End If
Set RangeFound = SearchRange.Find(What:=FindWhat, _
After:=StartingAfter, _
LookIn:=LookAtTextOrFormula, _
LookAt:=LookAtWholeOrPart, _
SearchOrder:=SearchRowCol, _
SearchDirection:=SearchUpDn, _
MatchCase:=bMatchCase)
End Function

[/COLOR]

[talkvinith]

Thanks You!!

[GTO]

You are very welcome :-)