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

X

Xcel90pro

New member
Joined
Jun 9, 2014
Messages
6
Reaction score
0
Points
0
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]       s = Found.Address[/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:
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
        s = Found.Address
        sMessage = """Orkis"" found in cell " & s & " Row: " & Found.Row & vbCr
    End If
    MsgBox sMessage


    With Range(ActiveCell, Found)
    
        .Value = .Value
    End With
End Sub
 
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:
You must log in or register to reply here.
Back
Top