Consulting

Results 1 to 3 of 3

Thread: Solved: Copy formula automatically & Paste valuse :VBA code error Rectification

  1. #1

    Solved: Copy formula automatically & Paste valuse :VBA code error Rectification

    Hi
    I have below code that needs to be rectified, while its getting executed automatically, I am getting message as below.

    And this code is copying formula, I need this to copy only values (result of formula which is not working here.

    attached file for your reference. Kindly help

    Error message
    Run time error '13':
    Type mismatch

    [VBA]
    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iRow As Long
    With Worksheets("Email Tracker")
    If Target.Columns = "Completed" Then
    iRow = .Cells(.Rows.Count, "F:F").End(xlUp).Row
    With .Range("H5", .Cells(iRow, "H"))
    .Formula = "=IF(RC[-2]=""Completed"",NOW(),"""")"
    .Value = .Value
    End With
    End If
    End With
    End Sub
    [/VBA]
    Attached Files Attached Files

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,247
    Location
    [VBA]Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iRow As Long

    On Error GoTo ws_exit

    Application.EnableEvents = False

    If Not Intersect(Target, Me.Columns("F")) Is Nothing Then

    With Target

    If .Row > 4 And .Cells.Count = 1 Then

    If .Value = "Completed" Then

    With .Offset(0, 2)

    .Formula = "=IF(RC[-2]=""Completed"",NOW(),"""")"
    .Value = .Value
    End With
    End If
    End If
    End With
    End If

    ws_exit:
    Application.EnableEvents = True
    End Sub[/VBA]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    thank you very much xld, this code working amazingly.

Posting Permissions

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