Hi,
I know this is an old message but I went to use this code today and when I typed
<snip>
in Option Explicit
> Function Add_Data(NewData)
> Dim nRows=
</snip> Here I got an error message:"Compile Error: Expected: Expression" NRows = stayed highighted in red even when I typed in the rest of the code. Thinking I had made an error I copied and pasted the code from the message, same problem, so I retyped and still the same.
Has anything changed in Excel 2007 to cause this?
Regards
Tony
--- In ExcelVBA@yahoogroups.com, Paul Schreiner <schreiner_paul@...> wrote:
>
> Sure, simple enough to do.
> In fact, you can put the macro call in
> a sheet_change event and then it will 'automatically'
> add it whenever you change the cell contents!
>
> In a module, I created:
>
> Option Explicit
> Function Add_Data(NewData)
> Dim nRows
> nRows = Application.WorksheetFunction.CountA(Sheets("Data").Range("A1:A65000"))
> Sheets("Data").Cells(nRows + 1, 1) = Now()
> Sheets("Data").Cells(nRows + 1, 2) = NewData
> End Function
>
> (It assumes that there is ALWAYS a record in column "A")
>
> then, in the Sheet module, I created a Change Event:
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim stat
> If ((Target.Address = "$C$3")) Then
> If (Target.Value <> "") Then _
> stat = Add_Data(Target.Value)
> Range("C3").Select
> End If
> End Sub
>
>
> (it assumes that you're making the entries in cell C3.
>
> Paul
>
>
>
>
>
> ________________________________
> From: Tony <studiot@...>
> To: ExcelVBA@yahoogroups.com
> Sent: Monday, October 12, 2009 4:34:21 AM
> Subject: [ExcelVBA] Macro to copy data from one cell and add to next empty cell in another worksheet
>
>
> Hi all
>
> I would like to be able to enter data in a specific cell (c15) in a worksheet named "Daily Data", and then have run a macro that would take that cell entry and add it to the next empty row in column B in a worksheet named "Data"
>
> Any ideas gratefully received
>
> Thanks in advance for your help
>
> regards
>
> Tony
>
>
>
>
> [Non-text portions of this message have been removed]
>
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar