VBA Function error and display result in sheet
 

I have the following vba code and the FuncSpaces variable (always
either 9, 10 or eleven spaces) will not concatenate to the other
strings.

Function FuncSpaces(PayGp)

Dim x As String
Dim NoOfSpace As Variant
Set NoOfSpace = Sheets("EmpInfo").Range("Spaces")
Code = 780

If IsEmpty(ActiveCell) Then
Exit Function
End If

x = Application.VLookup(PayGp, NoOfSpace, 2, False)
FuncSpaces = x

End Function

Sub CreatePrn()

Dim EmpNo, PayGp, Code, Prn, EffDate, Sal As String

Range("A2").Select
EmpNo = ActiveCell.Offset(0, 1)
PayGp = ActiveCell.Offset(0, 4)
Code = 780
EffDate = ActiveCell.Offset(0, 5)
Sal = ActiveCell.Offset(0, 6)


Do
Call FuncSpaces(PayGp)

Prn = (PayGp) & (EmpNo) & (NoOfSpace) & (Code) & (EffDate) & (Sal)


ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, 1))


End Sub

I have watched all the variables and when the function runs it
displays in watch window as " " but when the sub concatenates
all the variables the spaces arent included.

Also, how do I place each instance of the concatenation in a different
sheet.

ie i want the final result of eg 00512345 7800101200415500 to be
placed into new worksheet, so I can then export as text file.

Your help is much appreciated.

Flick. x

VBA Function error and display result in sheet
 

Hi Flick,

Try and replace

Call FuncSpaces(PayGp)

with

NoOfSpace = FuncSpaces(PayGp)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Felicity Geronimo" wrote in message
om...
I have the following vba code and the FuncSpaces variable (always
either 9, 10 or eleven spaces) will not concatenate to the other
strings.

Function FuncSpaces(PayGp)

Dim x As String
Dim NoOfSpace As Variant
Set NoOfSpace = Sheets("EmpInfo").Range("Spaces")
Code = 780

If IsEmpty(ActiveCell) Then
Exit Function
End If

x = Application.VLookup(PayGp, NoOfSpace, 2, False)
FuncSpaces = x

End Function

Sub CreatePrn()

Dim EmpNo, PayGp, Code, Prn, EffDate, Sal As String

Range("A2").Select
EmpNo = ActiveCell.Offset(0, 1)
PayGp = ActiveCell.Offset(0, 4)
Code = 780
EffDate = ActiveCell.Offset(0, 5)
Sal = ActiveCell.Offset(0, 6)


Do
Call FuncSpaces(PayGp)

Prn = (PayGp) & (EmpNo) & (NoOfSpace) & (Code) & (EffDate) & (Sal)


ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, 1))


End Sub

I have watched all the variables and when the function runs it
displays in watch window as " " but when the sub concatenates
all the variables the spaces arent included.

Also, how do I place each instance of the concatenation in a different
sheet.

ie i want the final result of eg 00512345 7800101200415500 to be
placed into new worksheet, so I can then export as text file.

Your help is much appreciated.

Flick. x