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('Input s'!E2,EmpInfo!A:D,3,FALSE),"780",'Inputs'!F2,'Inpu ts'!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

As far as I can see your formula should work except for a minor typo:
=CONCATENATE('Inputs'!E2,'Inputs'!B2,VLOOKUP('Inpu ts'!E2,EmpInfo!A:D,3,FALSE),"780",'Inputs'!F2,'Inp uts'!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('Input s'!E2,EmpInfo!A:D,3,FALSE),"780",'Inputs'!F2,'Inpu ts'!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