How to detirmine the target row

M

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
 
F

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

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
 
F

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

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
 
M

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
 
M

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
Click to expand...
 
F

Frank Kabel

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

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
 
F

Frank Kabel

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

Minitman

Thanks Frank,

I'll give it a shot.

-Minitman



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

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
 
F

Frank Kabel

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.)
 
M

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
 
F

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
 
M

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
 
Top