We help IT Professionals succeed at work.

Create a log in an Excel range with Macro

dynamicweb09
dynamicweb09 asked
on
Hi All,

I have an excel sheet which is constantly updated by Bloomberg automatically. I need to create a log of any change in value in a particular cell to a different range within this sheet/workbook. Any new entry to the log should always be exactly below the previous entry.

For Example :
Value of A1 is changed to 10 -
An entry will be made in cell C1 as '10'
Again the value of A1 is changed to 20 -
An entry will be made in cell C2 as '20'
And so on....

Regards,
Joydip

Comment
Watch Question

place this macro in the worksheet code section of all sheets that you want monitored.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim msg As String

    msg = "Value of " & Replace(Target.Address, "$", "") & " is changed to '" & Target.Text & "'"
    Debug.Print msg

End Sub

Open in new window


output will be logged to the debug section. When you'd rather have it in a file, add

open "c:\log.txt" for append as #1
print #1, msg
close #1

Open in new window

Author

Commented:
Hi akoste,

I need to have it in a different range in the excel file itself. Every change in Cell A1 should be entered in the C column one after another.

Regards,
Joydip

Author

Commented:
Ooops! I'm so sorry, I got your name wrong!
CERTIFIED EXPERT

Commented:
Hi, Joydip.

Is the Bloomberg update now triggering a Change event?

Thanks,
Brian.

Author

Commented:
Hi Redmondb,

No it still does not :)

But this time I need to store the changes (as affected by the macro you had sent me) in a range one cell after another after each change.

Hope you can help me againg this time.

Regards,
Joydip

that is possible using

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1")) Is Nothing Then
        msg = "Value of " & Replace(Target.Address, "$", "") & " is changed to '" & Target.Text & "'"
        pos = Cells(Rows.Count, "C").End(xlUp).Row + 1
        Range("C" & pos) = msg
    End If

End Sub

Open in new window


Author

Commented:
Hi akoster,

Thanks alot! Needed exactly that.

Regards,
Joydip

Author

Commented:
Hi Akoster,

"output will be logged to the debug section"

How to get the debug section ?

Regards,
Joydip
Top Expert 2007

Commented:
Debug.Print msg
The debug session is a part of the vba code editor, it gets visible / highlighted when you press [Ctrl]-[G]

Explore More ContentExplore courses, solutions, and other research materials related to this topic.