Concatenate and vlookup function in vba

[Felicity Geronimo]

Hi,

I have the following problem:

I need use the following formula in a vba function: (but keep
failing)and then place the result in a text/prn file.

=CONCATENATE('Inputs'!E2,'Inputs'B2,VLOOKUP('Inputs'!E2,EmpInfo!A:D,3,FALSE),"780",'Inputs'!F2,'Inputs'!G2))

Eg.1

E2 = 012 (text field, usually has leading zero)
E2 = 05050 ((text field, usually has leading zero)
Vlookup answer = " " (5 spaces)
F2 = 01012005 (date)
G2 = 2377000 (salary of 23770.00 - formatted)

The result would be:

01205050 010120052377000

Eg.2

E2 = 010 (text field, usually has leading zero)
E2 = 12345(text field, usually has leading zero)
Vlookup answer = " " (6 spaces)
F2 = 05112005 (date)
G2 = 5555000 (salary of 555550.00 - formatted)

The result would be:

01012345 0511200555555000

I then need to do this for all the rows of data in the input sheet,
and then export the data as a text/prn file.

Your help would be much appreciated.

Kind Regards

Flick

[K Dales]

As far as I can see your formula should work except for a minor typo:
=CONCATENATE('Inputs'!E2,'Inputs'!B2,VLOOKUP('Inputs'!E2,EmpInfo!A:D,3,FALSE),"780",'Inputs'!F2,'Inputs'!G2))

Note the ! in 'Inputs'!B2


"Felicity Geronimo" wrote:

> Hi,
>
> I have the following problem:
>
> I need use the following formula in a vba function: (but keep
> failing)and then place the result in a text/prn file.
>
> =CONCATENATE('Inputs'!E2,'Inputs'B2,VLOOKUP('Inputs'!E2,EmpInfo!A:D,3,FALSE),"780",'Inputs'!F2,'Inputs'!G2))
>
> Eg.1
>
> E2 = 012 (text field, usually has leading zero)
> E2 = 05050 ((text field, usually has leading zero)
> Vlookup answer = " " (5 spaces)
> F2 = 01012005 (date)
> G2 = 2377000 (salary of 23770.00 - formatted)
>
> The result would be:
>
> 01205050 010120052377000
>
> Eg.2
>
> E2 = 010 (text field, usually has leading zero)
> E2 = 12345(text field, usually has leading zero)
> Vlookup answer = " " (6 spaces)
> F2 = 05112005 (date)
> G2 = 5555000 (salary of 555550.00 - formatted)
>
> The result would be:
>
> 01012345 0511200555555000
>
> I then need to do this for all the rows of data in the input sheet,
> and then export the data as a text/prn file.
>
> Your help would be much appreciated.
>
> Kind Regards
>
> Flick
>