Greetings,

I have this worksheet that I use to enter invoice information. I am
using it to double check the entered invoices and modify any that are
incorrect. I don't know how to replace the incorrect record with the
corrected one. I can get the info pasted down once I can determine
which row it came from. I just don't know how to find the row with
the same invoice number (invoice number to be pasted is in named range
SI_InvNo. This needs to be found in named range Invoice1)

Anybody have an idea?

TIA

-Minitman

Hi
you could use the worksheet function MATCH in your VBA code. something
like
row_number=application.worksheetfunction.match(inv oice_number,range("In
voice1"),0)

--
Regards
Frank Kabel
Frankfurt, Germany


Minitman wrote:
Greetings,

I have this worksheet that I use to enter invoice information. I am
using it to double check the entered invoices and modify any that are
incorrect. I don't know how to replace the incorrect record with the
corrected one. I can get the info pasted down once I can determine
which row it came from. I just don't know how to find the row with
the same invoice number (invoice number to be pasted is in named
range
SI_InvNo. This needs to be found in named range Invoice1)

Anybody have an idea?

TIA

-Minitman

Hey Frank,

Thanks for the quick response.

I don't quite understand this bit of code. I have two named ranges
and you referred to only one. Where do I put "SI_InvNo"? Also what
are row_number and invoice_number? And finally, what does the 0
indicate?

row_number= _
application.worksheetfunction.match(invoice_number ,range("Invoice1"),0)

-Minitman


On Wed, 14 Apr 2004 19:35:17 +0200, "Frank Kabel"
wrote:

Hi
you could use the worksheet function MATCH in your VBA code. something
like
row_number=application.worksheetfunction.match(in voice_number,range("In
voice1"),0)

Hi
do you want a VBA solution or a worksheet function. For the first one
add the line in your code. So use something like

sub foo()
Dim row_number 'this is a variable
row_number= application.worksheetfunction. _
match(Range("SI_InvNo"),range("Invoice1"),0)
msgbox row_number
end sub

the '0' idnicates that Match shoud look for an exact match (see the
Excel help for more about the MATCH function syntax)




--
Regards
Frank Kabel
Frankfurt, Germany


Minitman wrote:
Hey Frank,

Thanks for the quick response.

I don't quite understand this bit of code. I have two named ranges
and you referred to only one. Where do I put "SI_InvNo"? Also what
are row_number and invoice_number? And finally, what does the 0
indicate?

row_number= _

application.worksheetfunction.match(invoice_number ,range("Invoice1"),0)

-Minitman


On Wed, 14 Apr 2004 19:35:17 +0200, "Frank Kabel"
wrote:

Hi
you could use the worksheet function MATCH in your VBA code.
something like

row_number=application.worksheetfunction.match(inv oice_number,range("In
voice1"),0)

Thanks Frank.

That is what I was looking for. I had a bit of trouble getting Excel
help to come up with anything on "match", until I tried "MATCH" then
it came up.

-Minitman

On Wed, 14 Apr 2004 21:01:30 +0200, "Frank Kabel"
wrote:

Hi
do you want a VBA solution or a worksheet function. For the first one
add the line in your code. So use something like

sub foo()
Dim row_number 'this is a variable
row_number= application.worksheetfunction. _
match(Range("SI_InvNo"),range("Invoice1"),0)
msgbox row_number
end sub

the '0' idnicates that Match shoud look for an exact match (see the
Excel help for more about the MATCH function syntax)

Hey Frank,

I can't get the code to work!

I keep getting

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

Debug highlighted this whole row:
row_number= application.worksheetfunction. _
match(Range("SI_InvNo"),range("Invoice1"),0)

Any idea as to what is going on?

TIA

-Minitman



On Wed, 14 Apr 2004 14:30:54 -0500, Minitman
wrote:

Thanks Frank.

That is what I was looking for. I had a bit of trouble getting Excel
help to come up with anything on "match", until I tried "MATCH" then
it came up.

-Minitman

On Wed, 14 Apr 2004 21:01:30 +0200, "Frank Kabel"
wrote:

Hi
do you want a VBA solution or a worksheet function. For the first one
add the line in your code. So use something like

sub foo()
Dim row_number 'this is a variable
row_number= application.worksheetfunction. _
match(Range("SI_InvNo"),range("Invoice1"),0)
msgbox row_number
end sub

the '0' idnicates that Match shoud look for an exact match (see the
Excel help for more about the MATCH function syntax)

Anyone have any ideas?

TIA

-Minitman



On Wed, 14 Apr 2004 19:07:03 -0500, Minitman
wrote:

Hey Frank,

I can't get the code to work!

I keep getting

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

Debug highlighted this whole row:
row_number= application.worksheetfunction. _
match(Range("SI_InvNo"),range("Invoice1"),0)

Any idea as to what is going on?

TIA

-Minitman



On Wed, 14 Apr 2004 14:30:54 -0500, Minitman
wrote:

Thanks Frank.

That is what I was looking for. I had a bit of trouble getting Excel
help to come up with anything on "match", until I tried "MATCH" then
it came up.

-Minitman

On Wed, 14 Apr 2004 21:01:30 +0200, "Frank Kabel"
wrote:

Hi
do you want a VBA solution or a worksheet function. For the first one
add the line in your code. So use something like

sub foo()
Dim row_number 'this is a variable
row_number= application.worksheetfunction. _
match(Range("SI_InvNo"),range("Invoice1"),0)
msgbox row_number
end sub

the '0' idnicates that Match shoud look for an exact match (see the
Excel help for more about the MATCH function syntax)

Hi
make sure that both names really exist. Sound like either SI_InvNo or
INVOICE1 is not defined

--
Regards
Frank Kabel
Frankfurt, Germany


Minitman wrote:
Hey Frank,

I can't get the code to work!

I keep getting

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

Debug highlighted this whole row:
row_number= application.worksheetfunction. _
match(Range("SI_InvNo"),range("Invoice1"),0)

Any idea as to what is going on?

TIA

-Minitman



On Wed, 14 Apr 2004 14:30:54 -0500, Minitman

wrote:

Thanks Frank.

That is what I was looking for. I had a bit of trouble getting
Excel
help to come up with anything on "match", until I tried "MATCH" then
it came up.

-Minitman

On Wed, 14 Apr 2004 21:01:30 +0200, "Frank Kabel"
wrote:

Hi
do you want a VBA solution or a worksheet function. For the first
one add the line in your code. So use something like

sub foo()
Dim row_number 'this is a variable
row_number= application.worksheetfunction. _
match(Range("SI_InvNo"),range("Invoice1"),0)
msgbox row_number
end sub

the '0' idnicates that Match shoud look for an exact match (see the
Excel help for more about the MATCH function syntax)

Hey Frank,

I thought of that and changed their names back to the actual cells
they represented. SI_InvNo went back to being Enter!AP2 and INVOICE1
went back to being INV!A1:A1752. Did not help. I am still getting :

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

I keep going over the help files and they don't touch on this problem.

I am open to suggestions

TIA

-Minitman


On Thu, 15 Apr 2004 07:59:53 +0200, "Frank Kabel"
wrote:

Hi
make sure that both names really exist. Sound like either SI_InvNo or
INVOICE1 is not defined

--
Regards
Frank Kabel
Frankfurt, Germany


Minitman wrote:
Hey Frank,

I can't get the code to work!

I keep getting

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

Debug highlighted this whole row:
row_number= application.worksheetfunction. _
match(Range("SI_InvNo"),range("Invoice1"),0)

Any idea as to what is going on?

TIA

-Minitman

Hi
try
row_number= application.worksheetfunction. _
match(worksheets("Enter").Range("AP2"), _
worksheets("INV").range("A1:A1752"),0)


--
Regards
Frank Kabel
Frankfurt, Germany


Minitman wrote:
Hey Frank,

I thought of that and changed their names back to the actual cells
they represented. SI_InvNo went back to being Enter!AP2 and
INVOICE1
went back to being INV!A1:A1752. Did not help. I am still getting :

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

I keep going over the help files and they don't touch on this
problem.

I am open to suggestions

TIA

-Minitman


On Thu, 15 Apr 2004 07:59:53 +0200, "Frank Kabel"
wrote:

Hi
make sure that both names really exist. Sound like either SI_InvNo
or
INVOICE1 is not defined

--
Regards
Frank Kabel
Frankfurt, Germany


Minitman wrote:
Hey Frank,

I can't get the code to work!

I keep getting

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

Debug highlighted this whole row:
row_number= application.worksheetfunction. _
match(Range("SI_InvNo"),range("Invoice1"),0)

Any idea as to what is going on?

TIA

-Minitman

Thanks Frank,

I'll give it a shot.

-Minitman



On Thu, 15 Apr 2004 09:31:12 +0200, "Frank Kabel"
wrote:

Hi
try
row_number= application.worksheetfunction. _
match(worksheets("Enter").Range("AP2"), _
worksheets("INV").range("A1:A1752"),0)


--
Regards
Frank Kabel
Frankfurt, Germany


Minitman wrote:
Hey Frank,

I thought of that and changed their names back to the actual cells
they represented. SI_InvNo went back to being Enter!AP2 and
INVOICE1
went back to being INV!A1:A1752. Did not help. I am still getting :

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

I keep going over the help files and they don't touch on this
problem.

I am open to suggestions

TIA

-Minitman


On Thu, 15 Apr 2004 07:59:53 +0200, "Frank Kabel"
wrote:

Hi
make sure that both names really exist. Sound like either SI_InvNo
or
INVOICE1 is not defined

--
Regards
Frank Kabel
Frankfurt, Germany


Minitman wrote:
Hey Frank,

I can't get the code to work!

I keep getting

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

Debug highlighted this whole row:
row_number= application.worksheetfunction. _
match(Range("SI_InvNo"),range("Invoice1"),0)

Any idea as to what is going on?

TIA

-Minitman

Hey Frank,

That works as great.

I am not sure how to proceed with the nest step, which is to paste the
contents of cells Enter!C3, Enter!B4 and Enter!A5 on to the 2nd, 3rd
and 4th column of INV! on the chosen row. These are not the actual
cells, they were chosen for example only.

Could you show me, with this example, what this code looks like?


What I am lacking to finish this project, is an example of the code,
to transfer the corrections back to the original and overwrite the
mistakes with the new correct figures.

Again, thanks for the help, it is greatly appreciated.

-Minitman


On Thu, 15 Apr 2004 02:40:05 -0500, Minitman
wrote:

Thanks Frank,

I'll give it a shot.

-Minitman



On Thu, 15 Apr 2004 09:31:12 +0200, "Frank Kabel"
wrote:

Hi
try
row_number= application.worksheetfunction. _
match(worksheets("Enter").Range("AP2"), _
worksheets("INV").range("A1:A1752"),0)


--
Regards
Frank Kabel
Frankfurt, Germany


Minitman wrote:
Hey Frank,

I thought of that and changed their names back to the actual cells
they represented. SI_InvNo went back to being Enter!AP2 and
INVOICE1
went back to being INV!A1:A1752. Did not help. I am still getting :

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

I keep going over the help files and they don't touch on this
problem.

I am open to suggestions

TIA

-Minitman


On Thu, 15 Apr 2004 07:59:53 +0200, "Frank Kabel"
wrote:

Hi
make sure that both names really exist. Sound like either SI_InvNo
or
INVOICE1 is not defined

--
Regards
Frank Kabel
Frankfurt, Germany


Minitman wrote:
Hey Frank,

I can't get the code to work!

I keep getting

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

Debug highlighted this whole row:
row_number= application.worksheetfunction. _
match(Range("SI_InvNo"),range("Invoice1"),0)

Any idea as to what is going on?

TIA

-Minitman

Hi
untested but try

sub foo()
dim wks_target as worksheet
dim wks_source as worksheet
dim row_number as long

set wks_target=Worksheets("INV")
set wks_source = worksheets("Enter")

'get row_number
row_number= application.worksheetfunction. _
match(wks_source.Range("AP2"), _
wks_target.range("A1:A1752"),0)

'paste values
with wks_source
wks_target.cells(row_number,"B").value=.range("C3" ).value
wks_target.cells(row_number,"C").value=.range("B4" ).value
wks_target.cells(row_number,"D").value=.range("A5" ).value
end with
end sub


note: no error checking included (e.g. no match is found, etc.)

--
Regards
Frank Kabel
Frankfurt, Germany


Minitman wrote:
Hey Frank,

That works as great.

I am not sure how to proceed with the nest step, which is to paste
the
contents of cells Enter!C3, Enter!B4 and Enter!A5 on to the 2nd, 3rd
and 4th column of INV! on the chosen row. These are not the actual
cells, they were chosen for example only.

Could you show me, with this example, what this code looks like?


What I am lacking to finish this project, is an example of the code,
to transfer the corrections back to the original and overwrite the
mistakes with the new correct figures.

Again, thanks for the help, it is greatly appreciated.

-Minitman


On Thu, 15 Apr 2004 02:40:05 -0500, Minitman

wrote:

Thanks Frank,

I'll give it a shot.

-Minitman



On Thu, 15 Apr 2004 09:31:12 +0200, "Frank Kabel"
wrote:

Hi
try
row_number= application.worksheetfunction. _
match(worksheets("Enter").Range("AP2"), _
worksheets("INV").range("A1:A1752"),0)


--
Regards
Frank Kabel
Frankfurt, Germany


Minitman wrote:
Hey Frank,

I thought of that and changed their names back to the actual cells
they represented. SI_InvNo went back to being Enter!AP2 and
INVOICE1 went back to being INV!A1:A1752. Did not help. I am
still getting :

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

I keep going over the help files and they don't touch on this
problem.

I am open to suggestions

TIA

-Minitman


On Thu, 15 Apr 2004 07:59:53 +0200, "Frank Kabel"
wrote:

Hi
make sure that both names really exist. Sound like either
SI_InvNo or INVOICE1 is not defined

--
Regards
Frank Kabel
Frankfurt, Germany


Minitman wrote:
Hey Frank,

I can't get the code to work!

I keep getting

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

Debug highlighted this whole row:
row_number= application.worksheetfunction. _
match(Range("SI_InvNo"),range("Invoice1"),0)

Any idea as to what is going on?

TIA

-Minitman

Hey Frank,

Good news/bad news!

The code works, but is real slow (like 2 min to make 1 change).

Any ideas on how to speed it up?

-Minitman


On Thu, 15 Apr 2004 10:15:57 +0200, "Frank Kabel"
wrote:

dim wks_target as worksheet
dim wks_source as worksheet
dim row_number as long

set wks_target=Worksheets("INV")
set wks_source = worksheets("Enter")

'get row_number
row_number= application.worksheetfunction. _
match(wks_source.Range("AP2"), _
wks_target.range("A1:A1752"),0)

'paste values
with wks_source
wks_target.cells(row_number,"B").value=.range("C3" ).value
wks_target.cells(row_number,"C").value=.range("B4" ).value
wks_target.cells(row_number,"D").value=.range("A5" ).value
end with

Hi
this small code shouldn't take 2 minutes?

Though you may add
application.screenupdating = false
at the beginning and

application.screenupdating = true
at the end of this macro


--
Regards
Frank Kabel
Frankfurt, Germany


Minitman wrote:
Hey Frank,

Good news/bad news!

The code works, but is real slow (like 2 min to make 1 change).

Any ideas on how to speed it up?

-Minitman


On Thu, 15 Apr 2004 10:15:57 +0200, "Frank Kabel"
wrote:

dim wks_target as worksheet
dim wks_source as worksheet
dim row_number as long

set wks_target=Worksheets("INV")
set wks_source = worksheets("Enter")

'get row_number
row_number= application.worksheetfunction. _
match(wks_source.Range("AP2"), _
wks_target.range("A1:A1752"),0)

'paste values
with wks_source
wks_target.cells(row_number,"B").value=.range("C3" ).value
wks_target.cells(row_number,"C").value=.range("B4" ).value
wks_target.cells(row_number,"D").value=.range("A5" ).value
end with

Hey Frank,

Did I forget to mention that I did a little bit of modifying? I added
132 more cells to complete my form.

That application.screenupdating did the trick, now it is finishing in
about 4 seconds.

Thanks again.

-Minitman


On Thu, 15 Apr 2004 13:10:35 +0200, "Frank Kabel"
wrote:

Hi
this small code shouldn't take 2 minutes?

Though you may add
application.screenupdating = false
at the beginning and

application.screenupdating = true
at the end of this macro