• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Why my code does not copy and paste the value?

M

mhghg

Member
Hello All,
I am studying the VBA class and I sent my question to Rao a few weeks but still do not get the response. I need some one help me to find out why or if you can re write it in a better way so i can learn then it is perfect !.
My script is running ok when the user adds the Credit or deduct the leave from Master sheet. The Master sheet will be updated with the new calculations automatically after that. However I like to create the audit trail on Master sheet to keep track of the old figures before updated. I have written the sub Do update at the end of the macro in Leave worksheet and Overtime worksheet to copy and paste the old figure from active sheet to the log sheet. But I dont know why it does not paste it into the Log sheet.
I dont know why cannot upload my .xlsm file. please help
 
Here is the code
Code: 
Private Sub CommandButton1_Click()
On Error Resume Next
Dim Var_Row As Double
Dim Var_Col As Double

Dim objDate1 As Date, _
    objDate2 As Date
Dim Table1      As Range, _
    Table2      As Range, _
    Table3      As Range, _
    FoundCell   As Variant

'Find the column with header "Name" in and use its row and column
'values to specify the tables dynamically

With Sheet4
    Set FoundCell = .Cells.Find("Name").Offset(1, 0)
    Var_Row = .Cells(.Rows.Count, FoundCell.Column).End(xlUp).Row - FoundCell.Row + 1
    Set Table1 = FoundCell.Resize(Var_Row)
End With 'Calculation

With Sheet2
    Set FoundCell = .Cells.Find("Name").Offset(1, 0)
    Var_Row = .Cells(.Rows.Count, FoundCell.Column).End(xlUp).Row - FoundCell.Row + 1
   
    Set Table2 = FoundCell.Resize(Var_Row, 3)
    Set Table3 = FoundCell.Offset(0, -1).Resize(Var_Row)
    Set Table4 = FoundCell.Resize(Var_Row)
End With 'Master

'First Cell in the range

Var_Row = Sheet4.Range("H2").Row
Var_Col = Sheet4.Range("H2").Column

'Copy balance from Master to Calculation sheet for computation

For Each C1 In Table1
    NameVal = C1
If Sheet4.Cells(Var_Row, Var_Col - 3).Value = "TMIL" Then
   
    Sheet4.Cells(Var_Row, Var_Col) = Application.WorksheetFunction.VLookup(C1, Table2, 3, False)
    oldVal = Application.WorksheetFunction.VLookup(C1, Table2, 3, False)
    objDate2 = Application.WorksheetFunction.Index(Table3, Application.WorksheetFunction.Match(C1, Table4, 0), 1)
    objDate1 = Sheet4.Cells(Var_Row, Var_Col - 7).Value

'Comparing date before updating the master sheet Date and Old_TMIL

    'MsgBox "Date2: " & objDate2
    'MsgBox "Date1: " & objDate1
   
        If objDate1 > objDate2 Then
            With Sheet2
            .Cells(Application.WorksheetFunction.Match(C1, Table4, 0) + 1, 4).Value = Sheet4.Cells(Var_Row, Var_Col).Value + Sheet4.Cells(Var_Row, Var_Col + 2).Value
            .Cells(Application.WorksheetFunction.Match(C1, Table4, 0) + 1, 1).Value = Sheet4.Cells(Var_Row, 1).Value
            NewVal = .Cells(Application.WorksheetFunction.Match(C1, Table4, 0) + 1, 4).Value
            End With 'Master
        End If
        Call DoUpdate(oldVal, NewVal, NameVal)
       
End If

If Sheet4.Cells(Var_Row, Var_Col - 3).Value = "A/Leave" Then
   
    Sheet4.Cells(Var_Row, Var_Col + 1) = Application.WorksheetFunction.VLookup(C1, Table2, 2, False)
    oldVal = Application.WorksheetFunction.VLookup(C1, Table2, 2, False)
    objDate2 = CDate(Application.WorksheetFunction.Index(Table3, Application.WorksheetFunction.Match(C1, Table4, 0), 1))
    objDate1 = CDate(Sheet4.Cells(Var_Row, Var_Col - 7).Value)

'Comparing date before updating the master sheet Date and Old_Balance

        If objDate1 > objDate2 Then
             With Sheet2
            .Cells(Application.WorksheetFunction.Match(C1, Table4, 0) + 1, 3).Value = Sheet4.Cells(Var_Row, Var_Col + 1).Value + Sheet4.Cells(Var_Row, Var_Col + 3).Value
            .Cells(Application.WorksheetFunction.Match(C1, Table4, 0) + 1, 1).Value = Sheet4.Cells(Var_Row, 1).Value
            NewVal = .Cells(Application.WorksheetFunction.Match(C1, Table4, 0) + 1, 3).Value
            End With 'Master
        End If
        Call DoUpdate(oldVal, NewVal, NameVal)
End If

Var_Row = Var_Row + 1
Next C1
       
MsgBox "Done"

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    'Column has the data is B column
    If Target.Column = 2 Then
        Application.EnableEvents = False
        Cells(Target.Row, 1).Value = Date + Time
        Application.EnableEvents = True
    End If
End Sub

Sub DoUpdate(oldVal, NewVal, NameVal)
Dim Table5 As Range
Dim Var_Row As Double, Var_Col As Double
With Sheet5
    Set FoundCell = .Cells.Find("Date").Offset(1, 0)
    Var_Row = .Cells(.Rows.Count, FoundCell.Column).End(xlUp).Row - FoundCell.Row + 1
    Set Table5 = FoundCell.Resize(Var_Row)
End With 'Calculation

Var_Row = Sheet5.Range("A2").Row
Var_Col = Sheet5.Range("A2").Column
   
   
    MsgBox ("old Value: ") & oldVal
    MsgBox ("new Value: ") & NewVal
     
    If NewVal <> oldVal Then
      Sheet5.Cells(Var_Row, Var_Col).Value = Date & " " & Time
      Sheet5.Cells(Var_Row, Var_Col + 2).Value = Application.UserName
      Sheet5.Cells(Var_Row, Var_Col + 3).Value = NameVal
      Sheet5.Cells(Var_Row, Var_Col + 4).Value = "Value has changed from " & oldVal & " to" & NewVal
    End If
       
Var_Row = Var_Row + 1
End Sub
[\code]
 

Hi,

bad use of Find method, see its VBA help example : the way how to use it …

For worksheet functions, remove WorksheetFunction,
Application is enough combined with a Variant variable.

Remove On Error Resume Next and you'll find out the error source !

Hit F8 key to progress in code in step by step mode to watch out worksheet
and values in Local variables window …​
 
Last edited:

I never use WorkSheetFunction in final code
except if I want the code stops in case of error !

Example without in my Demo2 procedure in codeline #17 of this thread …​
 
Last edited:
Thank you all. I have rewrite my code and it is a bit better. I can copy and paste the value to the log sheet. But i have the problem to add the new entry to the log file when I switch from the debit to credit sheet. I attach the sample file again too.

The code for copying below
Code: 
With Sheet7
  .Cells(Var_Row, Var_Col).Value = Date + Time
  .Cells(Var_Row, Var_Col + 1).Value = C1
  .Cells(Var_Row, Var_Col + 2).Value = "The TMIL balance has been changed from " & oldValue & " to " & newValue
  End With 'Log file
[\code]
 

Attachments

  • Master_Balance Jan 2015.xlsm
    41.8 KB · Views: 0
Last edited:
You must log in or register to reply here.
Back
Top