Hi Tony
It doesn't say
dim nrows=
It says
dim nrows
nrows=
Application.WorksheetFunction.CountA(Sheets("Data").Range("A1:A65000")
)
Dim nrows should be on one line
The following rows of the above text should all be on the next line. (from
nrows= to the closing bracket) They got split into three in the email.
Best Regards
David Grugeon
Excel VBA Group Moderator
-----Original Message-----
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf
Of Tony
Sent: Tuesday, 19 July 2011 9:51 PM
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Re: Macro to copy data from one cell and add to next
empty cell in another worksheet
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
----------------------------------Yahoo! Groups Links
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