Urgent Help VBA

J

Jeff

Hello,


I need ASAP to transform this function into a VBA macro to finish an
existing VBA Macro

=INDEX([VBPA.xls]Sheet1!$D$1:$D$50000,
MATCH(1,(INT(D3)=[VBPA.xls]Sheet1!$A$1:$A$50000)*("BP"=[VBPA.xls]Sheet1!$C$1:$C$50000),0))
 
Z

Zack Barresse

Hello,

A function is different than a procedure, they are not the same, and that is
what you asked for. If you want to know how to *use* the function using
VBA, well, why don't you use it just the way it is, using the Evaluate
method?

Evaluate("=INDEX([VBPA.xls]Sheet1!$D$1:$D$50000,MATCH(1,(INT(D3)=[VBPA.xls]Sheet1!$A$1:$A$50000)*(""BP""=[VBPA.xls]Sheet1!$C$1:$C$50000),0))")

(did not test your formula) Just be sure and use double quotes around your
text in the formula, single quotes will skew it in VBA.
 
J

Jeff

Thank you.
So, can anyone please help me with a VBA procedure that would run this
function from row F3 to the next empty row in column "C"

=INDEX([VBPA.xls]Sheet1!$D$1:$D$50000,
MATCH(1,(INT(D3)=[VBPA.xls]Sheet1!$A$1:$A$50000)*("BP"=[VBPA.xls]Sheet1!$C$1:$C$50000),0))
Click to expand...
Click to expand...
--
Regards,
Jeff



Zack Barresse said:
Hello,

A function is different than a procedure, they are not the same, and that is
what you asked for. If you want to know how to *use* the function using
VBA, well, why don't you use it just the way it is, using the Evaluate
method?

Evaluate("=INDEX([VBPA.xls]Sheet1!$D$1:$D$50000,MATCH(1,(INT(D3)=[VBPA.xls]Sheet1!$A$1:$A$50000)*(""BP""=[VBPA.xls]Sheet1!$C$1:$C$50000),0))")

(did not test your formula) Just be sure and use double quotes around your
text in the formula, single quotes will skew it in VBA.

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


Jeff said:
Hello,


I need ASAP to transform this function into a VBA macro to finish an
existing VBA Macro

=INDEX([VBPA.xls]Sheet1!$D$1:$D$50000,
MATCH(1,(INT(D3)=[VBPA.xls]Sheet1!$A$1:$A$50000)*("BP"=[VBPA.xls]Sheet1!$C$1:$C$50000),0))
Click to expand...
Click to expand...
 
Z

Zack Barresse

Okay, I'm confused. Can't you do this by hand? I think it should only take
about 10 seconds or so.. :-?

Goto C3, press Ctrl + Down
Press Down arrow one more time
Press Right arrow 3 times.
Hold Shift, click F3
Press Enter once
Enter formula
Confirm with Ctrl + Enter

Am I missing something here... ??

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)



Jeff said:
Thank you.
So, can anyone please help me with a VBA procedure that would run this
function from row F3 to the next empty row in column "C"

=INDEX([VBPA.xls]Sheet1!$D$1:$D$50000,
MATCH(1,(INT(D3)=[VBPA.xls]Sheet1!$A$1:$A$50000)*("BP"=[VBPA.xls]Sheet1!$C$1:$C$50000),0))
Click to expand...
Click to expand...
--
Regards,
Jeff



Zack Barresse said:
Hello,

A function is different than a procedure, they are not the same, and that
is
what you asked for. If you want to know how to *use* the function using
VBA, well, why don't you use it just the way it is, using the Evaluate
method?

Evaluate("=INDEX([VBPA.xls]Sheet1!$D$1:$D$50000,MATCH(1,(INT(D3)=[VBPA.xls]Sheet1!$A$1:$A$50000)*(""BP""=[VBPA.xls]Sheet1!$C$1:$C$50000),0))")

(did not test your formula) Just be sure and use double quotes around
your
text in the formula, single quotes will skew it in VBA.

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


Jeff said:
Hello,


I need ASAP to transform this function into a VBA macro to finish an
existing VBA Macro

=INDEX([VBPA.xls]Sheet1!$D$1:$D$50000,
MATCH(1,(INT(D3)=[VBPA.xls]Sheet1!$A$1:$A$50000)*("BP"=[VBPA.xls]Sheet1!$C$1:$C$50000),0))
Click to expand...
Click to expand...
Click to expand...
 
Top