Re: [ExcelVBA] Re: Macro to copy data from one cell and add to next empty cell in another worksheet

 

Difficult to see what you've actually put in your code from what you have
here, but

in Option Explicit

should just be Option Explicit (i.e. no word "in"). It is a compiler
directive that is placed by itself at the top of your module, and is not
part of the actual code.

> Function Add_Data(NewData)

If you have the > sign, then you shouldn't. That's a marker from the
e-mail.

> Dim nRows=

Again, there shouldn't be a > sign. Also, though, Dim statements don't have
= signs in them.

If this doesn't help, please provide an unsnipped copy/paste from your code
into a message, so that we know exactly what your code looks like.

Looking at the e-mail you quoted, there is

> Option Explicit
> Function Add_Data(NewData)
> Dim nRows
> nRows =
> Application.WorksheetFunction.CountA(Sheets("Data").Range("A1:A65000"))

... certainly no equals sign on the Dim statement.

Regards, Dave S

----- Original Message -----
From: "Tony" <studiot@gmail.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Tuesday, July 19, 2011 9:51 PM
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

-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1390 / Virus Database: 1518/3774 - Release Date: 07/19/11

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (8)

Recent Activity:

• New Members 9

Visit Your Group

----------------------------------
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

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___