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 :-)