Conversion from Equation to Values not a normal -Special paste scenario

Xcel90pro

New member
I"m writing a code to search for a spefic word to find the amount of rows need then convert all of the formulas in two columns down to the row found early from formulas/macro equations to values. Here is what i got so Far just cant figure out the convert part inside the do loop

Thanks Guys

Code:
Sub converttt()[/COLOR]
[COLOR=#333333]'[/COLOR]
[COLOR=#333333]' converttt Macro[/COLOR]
[COLOR=#333333]'[/COLOR]

[COLOR=#333333]''Sub Find_Accounts()[/COLOR]
[COLOR=#333333]Dim Found As Range[/COLOR]
[COLOR=#333333]Dim sMessage As String[/COLOR]
[COLOR=#333333]Dim s As String[/COLOR]
[COLOR=#333333]Dim d As String[/COLOR]
[COLOR=#333333]'[/COLOR]
[COLOR=#333333]   Set Found = Columns("A").Find(What:="Orkis", After:=[A1], LookIn:=xlFormulas, _[/COLOR]
[COLOR=#333333]   LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _[/COLOR]
[COLOR=#333333]   MatchCase:=False, SearchFormat:=False)[/COLOR]
[COLOR=#333333]   If Found Is Nothing Then[/COLOR]
[COLOR=#333333]       sMessage = "No Match Found for ""Orkis""" & vbCr[/COLOR]
[COLOR=#333333]   Else[/COLOR]
[COLOR=#333333]       sMessage = """Orkis"" found in cell " & Found.Address & " Row: " & Found.Row & vbCr[/COLOR]
[COLOR=#333333]   End If[/COLOR]
[COLOR=#333333]   MsgBox sMessage[/COLOR]

[COLOR=#333333]   Do until activecell.Value = s[/COLOR]

[COLOR=#333333]       ActivateCell.Offset(1.0).select[/COLOR]
Loop
[COLOR=#333333]End Sub[/COLOR]

Last edited by a moderator:

Bob Phillips

Super Moderator
Staff member
Code:
Sub converttt()
Dim Found As Range
Dim sMessage As String
Dim s As String
Dim d As String
'
Set Found = Columns("A").Find(What:="Orkis", After:=[A1], LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Found Is Nothing Then
sMessage = "No Match Found for ""Orkis""" & vbCr
Else
sMessage = """Orkis"" found in cell " & s & " Row: " & Found.Row & vbCr
End If
MsgBox sMessage

With Range(ActiveCell, Found)

.Value = .Value
End With
End Sub

snb

New member
Code:
Sub M_snb()
on error resume next
with range(cells(2,1),Columns(1).Find("Orkis", cells(1),,1))
.value=.value
end with

msgbox iif(err.number<>0,"not ","") & "successful"
End Sub

The VBA code tags are a disaster.

Last edited: