Checking a database/excel sheet for new entries
 

Hello everybody,

I was wondering if i can get some help with a problem i have. I
currently have a worksheet in Excel which holds monthly transaction
information for my company. What I normally do is copy transaction
info from another program and paste it into the next available cell
(starting in column A) in Excel. There are 43 columns/fields in this
worksheet. So here's what I need:

Once I copy in the information to the next available cell, i want to
be able to run a macro which would compare the contents in column A
(e.g. A160) with the rest of the entries (A1:A159) - if there is a
match in the transaction number, I'd like Excel to put in "Old
transaction" in the corresponding cell in column AQ (the 43rd column).
I automatically put in "New transaction" for any new entries. Please
note that I copy and paste up to 8 rows of data into the worksheet
before running the macro. All i need is the logic to compare whatever
I pasted in last (up to 8 entries) with what ever was in the worksheet
before. Thanks for any and all efforts to help.

Tim

Checking a database/excel sheet for new entries
 

wrote in message
ups.com...
Hello everybody,

I was wondering if i can get some help with a problem i have. I
currently have a worksheet in Excel which holds monthly transaction
information for my company. What I normally do is copy transaction
info from another program and paste it into the next available cell
(starting in column A) in Excel. There are 43 columns/fields in this
worksheet. So here's what I need:

Once I copy in the information to the next available cell, i want to
be able to run a macro which would compare the contents in column A
(e.g. A160) with the rest of the entries (A1:A159) - if there is a
match in the transaction number, I'd like Excel to put in "Old
transaction" in the corresponding cell in column AQ (the 43rd column).
I automatically put in "New transaction" for any new entries. Please
note that I copy and paste up to 8 rows of data into the worksheet
before running the macro. All i need is the logic to compare whatever
I pasted in last (up to 8 entries) with what ever was in the worksheet
before. Thanks for any and all efforts to help.

Tim


Hi Tim

Here is off the cuff code which will do what you want in demo form. You
candefintely improve on this but it should get you started with smarter.

Creat a new work sheet

IN A1 type transaction

Fill A2 to what ever you want with trial data

Add some new data to the end of the column and then SELECT THE NEW DATA
with the mouse.

Leaving the new data selected run this macro ( you need to install the macro
first of course)

Sub newcheck()


Columns("C:C").Clear

Dim task As Range
Dim myrange As Range
Dim x, q, b As Integer
Dim rws As Integer
Dim ax As String

Set task = Selection
task.Select
rws = task.Rows.Count
ReDim targs(rws)

For Each cell In task
x = x + 1
targs(x) = cell.Value
Next

Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Dim task2 As Range
Set task2 = Selection
q = task2.Rows.Count
Range("A2").Select
q = q - rws
ax = "a" & q

Set myrange = Range("A2", ax)

For b = 1 To x

For Each cell In myrange
If cell.Value = targs(b) Then cell.Offset(0, 2).Value = " Old Transaction"
Next
Next

End Sub

You can fix this up to meet your own requirments once you have tested in,

Best N10

Checking a database/excel sheet for new entries
 

On Jun 20, 7:22 pm, "N10" wrote:
wrote in message

ups.com...





Hello everybody,

I was wondering if i can get some help with a problem i have. I
currently have a worksheet in Excel which holds monthly transaction
information for my company. What I normally do is copy transaction
info from another program and paste it into the next available cell
(starting in column A) in Excel. There are 43 columns/fields in this
worksheet. So here's what I need:

Once I copy in the information to the next available cell, i want to
be able to run a macro which would compare the contents in column A
(e.g. A160) with the rest of the entries (A1:A159) - if there is a
match in the transaction number, I'd like Excel to put in "Old
transaction" in the corresponding cell in column AQ (the 43rd column).
I automatically put in "New transaction" for any new entries. Please
note that I copy and paste up to 8 rows of data into the worksheet
before running the macro. All i need is the logic to compare whatever
I pasted in last (up to 8 entries) with what ever was in the worksheet
before. Thanks for any and all efforts to help.

Tim

Hi Tim

Here is off the cuff code which will do what you want in demo form. You
candefintely improve on this but it should get you started with smarter.

Creat a new work sheet

IN A1 type transaction

Fill A2 to what ever you want with trial data

Add some new data to the end of the column and then SELECT THE NEW DATA
with the mouse.

Leaving the new data selected run this macro ( you need to install the macro
first of course)

Sub newcheck()

Columns("C:C").Clear

Dim task As Range
Dim myrange As Range
Dim x, q, b As Integer
Dim rws As Integer
Dim ax As String

Set task = Selection
task.Select
rws = task.Rows.Count
ReDim targs(rws)

For Each cell In task
x = x + 1
targs(x) = cell.Value
Next

Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Dim task2 As Range
Set task2 = Selection
q = task2.Rows.Count
Range("A2").Select
q = q - rws
ax = "a" & q

Set myrange = Range("A2", ax)

For b = 1 To x

For Each cell In myrange
If cell.Value = targs(b) Then cell.Offset(0, 2).Value = " Old Transaction"
Next
Next

End Sub

You can fix this up to meet your own requirments once you have tested in,

Best N10- Hide quoted text -

- Show quoted text -

Hi!

Thanks for taking the time out to help me with my problem. However,
the above code fails at targs(x) = cell.Value.

Anyways, since i'm not the greatest programmer, i wasn't able to
achieve what i needed and this didn't help me achieve what i wanted.
Thanks for your help and sorry to have wasted our time - i will repost
this again in hopes of getting a specific answer/solution to the
problem. Thanks again.