Results 1 to 3 of 3

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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()
    Code:
    '
    ' converttt Macro
    '
    
    
    ''Sub Find_Accounts()
    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 " & Found.Address & " Row: " & Found.Row & vbCr
           s = Found.Address
       End If
       MsgBox sMessage
    
       Do until activecell.Value = s
    
           ActivateCell.Offset(1.0).select
        Loop
    End Sub
    Last edited by Bob Phillips; 2014-06-09 at 11:54 PM. Reason: Added VBA tags

  2. #2
    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

  3. #3
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    374
    Articles
    0
    Excel Version
    2020
    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 by snb; 2014-06-10 at 08:04 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •