How to detirmine the target row

[Minitman]

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

 

[Frank Kabel]

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

 

[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

 

[Frank Kabel]

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)

 

[Minitman]

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

 

[Minitman]

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

 

[Minitman]

Anyone have any ideas?

TIA

-Minitman

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

 

[Frank Kabel]

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

 

[Minitman]

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

 

[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

 

[Frank Kabel]

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

 

[Minitman]

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