I have in the spreadsheet:

the columns contain descriptions of events and each event can have an error
code attached.
Log_off Fill in form Section blank
row 1 2 4 5

the errorcode table on another sheet

Log_off 1 logged off correctly no action
Log_off 2 logged off incorectly teach how to log off

Fill in form 1 Filled in correctly no action
Fill in form 2 filled form incorectly teach how to fill in
form

How do I do the vlookup in this case as if I just used the numerical column
it wouldnt know which 1 of 1 or 2 of 2 to return.

Logically it wants to lookup the first column ie Vlookup description of
error and then vlookup the number within that desription.


Hope that makes sense.

Regards
Peter

Put up the code you have written/how you are using vlookup.

--
Regards,
Tom Ogilvy


"Peter Bailey" wrote in message
...
I have in the spreadsheet:

the columns contain descriptions of events and each event can have an
error
code attached.
Log_off Fill in form Section blank
row 1 2 4 5

the errorcode table on another sheet

Log_off 1 logged off correctly no action
Log_off 2 logged off incorectly teach how to log off

Fill in form 1 Filled in correctly no action
Fill in form 2 filled form incorectly teach how to fill in
form

How do I do the vlookup in this case as if I just used the numerical
column
it wouldnt know which 1 of 1 or 2 of 2 to return.

Logically it wants to lookup the first column ie Vlookup description of
error and then vlookup the number within that desription.


Hope that makes sense.

Regards
Peter

here it is in its entirity

Dim ErrorCount As Integer
Dim LearnerCount As Integer
Dim ErrorArray() As Variant
Dim intx As Integer
Dim count As Integer
Dim x As Integer
Dim Cell As Range
Dim OutputString As String

LearnerCount = Worksheets("Error Codes").Range("C2").Value
If LearnerCount = 0 Then
'no learners entered on form so send message and exit
MsgBox ("You attempted to run the macro that adds error actions to a
form with no learners entered on it!")
Else
'records exist so work
count = 0
'redimension array to learner count plus add 15 cells to store error data
ReDim ErrorArray(LearnerCount - 1, 15)
For intx = 0 To LearnerCount - 1 ' for each learner change row offset
For Each Cell In Range("C10:T10") 'set range
Select Case Cell.Column
' get the value
Case 3: ErrorArray(intx, count) = Cell.Offset(intx, 0).Value
count = count + 1
OutputString = Cell.Offset(intx, 0).Value
Case 4 To 5: ' dont get value from these columns
' get the value
Case 6 To 20: ErrorArray(intx, count) = Cell.Offset(intx,
0).Value
count = count + 1
If (Cell.Offset(intx, 0).Value) 1 Then
OutputString = OutputString &
CStr(WorksheetFunction.VLookup(Cell.Column, "B7:B71",0)) its all wrong here
end if
End Select
Next
count = 0
Next intx


End If

here is the error data table

Column cell number Code Action
6 1 No action needed
6 2 Please sign and then fax the Individual Learner Agreement to the
Hub urgently.
6 3 Please get the Learner to sign the Individual Learner Agreement
then fax ILA to the Hub urgently.
6 4 Learner & Centre to sign Individual Learner Agreement and fax to
Hub urgently.
6 5 Complete the fee remission section in the Independent Learner
Agreement
6 6 Complete all sections of the Individual Learner Agreement

7 1 No action needed
7 2 No action needed
7 3 Fax Proof of benefits to Hub or change field A14 on ILR
learning aim details section to reflect correct status
7 4 Fax proof of a means tested benefit to the Hub or change field A14
on ILR to reflect correct status

8 1 No action needed
8 2 On ILR Maintenance screen set status to claim & save then amend
corrections as necessary as reported and let hub know.
8 3 On ILR Learning aim data screen set status to claim & save then
amend corrections as necessary as reported and let hub know.
8 4 Enter The end date in field A31 On ILR Learning aim data screen
and ensure appropriate data in fields A33, A35 and A39 & save and let hub
know
8 5 If learner has achieved get the learner to tick their achieved
learner goal box otherwise change fields A35 and A39 appropriately.
8 6 Check and correct the fee remission amount in field A13 On ILR
Learning aim data screen
8 7 Provide evidence of achievement or set fields A34, A35 & A39
appropriately to show continuing study and no achievement funding claimed.
And untick achievement box and notify hub.

9 1 No action needed
9 2 Hub to investigate with quality audit team, centre need not take
action at this stage.
9 3 Hub to investigate with quality audit team, centre need not take
action at this stage.

10 1 No action needed
10 2 Evidence required. Send to hub manual logs to show attendance and
update tutor confirmation notes to show that course was offline or from a
workbook.
10 3 Ensure that centre has adequate training material to assist and
ensure learners log off correctly.
10 4 Provide the hub with evidence of learning unless the course was
withdrawn.

11 1 No action needed
11 2 Provide hub with assessment evidence of Skills for life need then
amend fileds A14 and A10 appropriately.
11 3 Provide hub with assessment evidence of Skills for life need then
amend fileds A14 and A10 appropriately.

12 1 No action needed
12 2 Centre must investigate why goal has not been entered and hub
will pass details to quality audit team.
12 3 Centre must investigate why goal has is not a 'SMART' goal and
hub will pass details to quality audit team.

13 1 No action needed
13 2 Speak to tutors to ensure they understand about adding
outcomes/progression notes. The hub will pass the details to the quality
audit team.
13 3 Please speak to tutors to ensure they understand the importance
of adding Tutor support notes. Funding may be deducted as a result of this.
13 4 Please speak to tutors to ensure they understand the importance
of adding Tutor support notes and about adding outcome / progression notes.
Funding may be deducted as a result of this.
13 5 Please speak to tutors to ensure they understand the importance
of adding good Tutor support notes and about adding good outcome /
progression notes. Funding may be deducted as a result of this.

14 1 No action needed
14 2 Please look at learner's notes and encourage them to be more
course specific with tutor support or to log more.
14 3 Please encourage learners to log tutor support and also log this
at a tutor level. Funding may be deducted as a result of this.

15 1 No action needed
15 2 Please get learner to tick their outcomes otherwise funding will
not be automatically triggered.

16 1 No action needed
16 2 Please get tutor to confirm all learners ticked outcomes,
otherwise funding will not be automatically triggered.
16 3 Please get learner to tick outcomes ASAP! Funding may be
deducted as a result of this. This may also be passed to the Hubs Quality
Audit Team.

17 1 No action needed
17 2 No action needed
17 3 No action needed
17 4 Please check to see if learner has achieved their goal, if so get
them to tick their box, if the learner has not achieved then there is no
action required.

18 1 No action needed
18 2 Please get the tutor to confirm the learners ticked goal.
Achievement funding may be deducted as a result of this. This may also be
passed to the Hubs Quality Audit Team.
18 3 Please get learner to tick Goal as the achievement funding may be
deducted as a result of this. This may also be passed to the Hubs Quality
Audit Team.

19 1 No action needed
19 2 Please send the learners ILP in to the HUB ASAP!



"Tom Ogilvy" wrote in message
...
Put up the code you have written/how you are using vlookup.

--
Regards,
Tom Ogilvy


"Peter Bailey" wrote in message
...
I have in the spreadsheet:

the columns contain descriptions of events and each event can have an
error
code attached.
Log_off Fill in form Section blank
row 1 2 4 5

the errorcode table on another sheet

Log_off 1 logged off correctly no action
Log_off 2 logged off incorectly teach how to log off

Fill in form 1 Filled in correctly no action
Fill in form 2 filled form incorectly teach how to fill in
form

How do I do the vlookup in this case as if I just used the numerical
column
it wouldnt know which 1 of 1 or 2 of 2 to return.

Logically it wants to lookup the first column ie Vlookup description of
error and then vlookup the number within that desription.


Hope that makes sense.

Regards
Peter

WorksheetFunction.VLookup(Cell.Column, "B7:B71",0)

would be

WorksheetFunction.VLookup(Cell.Column, Range("B7:B71"),0)


Range("b7:b71") may need to be qualified by Worksheets("SheetWhatever") if
it is not on the activesheet.

--
Regards,
Tom Ogilvy


"Peter Bailey" wrote in message
...
here it is in its entirity

Dim ErrorCount As Integer
Dim LearnerCount As Integer
Dim ErrorArray() As Variant
Dim intx As Integer
Dim count As Integer
Dim x As Integer
Dim Cell As Range
Dim OutputString As String

LearnerCount = Worksheets("Error Codes").Range("C2").Value
If LearnerCount = 0 Then
'no learners entered on form so send message and exit
MsgBox ("You attempted to run the macro that adds error actions to a
form with no learners entered on it!")
Else
'records exist so work
count = 0
'redimension array to learner count plus add 15 cells to store error data
ReDim ErrorArray(LearnerCount - 1, 15)
For intx = 0 To LearnerCount - 1 ' for each learner change row offset
For Each Cell In Range("C10:T10") 'set range
Select Case Cell.Column
' get the value
Case 3: ErrorArray(intx, count) = Cell.Offset(intx, 0).Value
count = count + 1
OutputString = Cell.Offset(intx, 0).Value
Case 4 To 5: ' dont get value from these columns
' get the value
Case 6 To 20: ErrorArray(intx, count) = Cell.Offset(intx,
0).Value
count = count + 1
If (Cell.Offset(intx, 0).Value) 1 Then
OutputString = OutputString &
CStr(WorksheetFunction.VLookup(Cell.Column, "B7:B71",0)) its all wrong
here
end if
End Select
Next
count = 0
Next intx


End If

here is the error data table

Column cell number Code Action
6 1 No action needed
6 2 Please sign and then fax the Individual Learner Agreement to
the
Hub urgently.
6 3 Please get the Learner to sign the Individual Learner
Agreement
then fax ILA to the Hub urgently.
6 4 Learner & Centre to sign Individual Learner Agreement and fax
to
Hub urgently.
6 5 Complete the fee remission section in the Independent Learner
Agreement
6 6 Complete all sections of the Individual Learner Agreement

7 1 No action needed
7 2 No action needed
7 3 Fax Proof of benefits to Hub or change field A14 on ILR
learning aim details section to reflect correct status
7 4 Fax proof of a means tested benefit to the Hub or change field
A14
on ILR to reflect correct status

8 1 No action needed
8 2 On ILR Maintenance screen set status to claim & save then amend
corrections as necessary as reported and let hub know.
8 3 On ILR Learning aim data screen set status to claim & save then
amend corrections as necessary as reported and let hub know.
8 4 Enter The end date in field A31 On ILR Learning aim data screen
and ensure appropriate data in fields A33, A35 and A39 & save and let hub
know
8 5 If learner has achieved get the learner to tick their achieved
learner goal box otherwise change fields A35 and A39 appropriately.
8 6 Check and correct the fee remission amount in field A13 On ILR
Learning aim data screen
8 7 Provide evidence of achievement or set fields A34, A35 & A39
appropriately to show continuing study and no achievement funding claimed.
And untick achievement box and notify hub.

9 1 No action needed
9 2 Hub to investigate with quality audit team, centre need not take
action at this stage.
9 3 Hub to investigate with quality audit team, centre need not take
action at this stage.

10 1 No action needed
10 2 Evidence required. Send to hub manual logs to show attendance
and
update tutor confirmation notes to show that course was offline or from a
workbook.
10 3 Ensure that centre has adequate training material to assist and
ensure learners log off correctly.
10 4 Provide the hub with evidence of learning unless the course was
withdrawn.

11 1 No action needed
11 2 Provide hub with assessment evidence of Skills for life need
then
amend fileds A14 and A10 appropriately.
11 3 Provide hub with assessment evidence of Skills for life need
then
amend fileds A14 and A10 appropriately.

12 1 No action needed
12 2 Centre must investigate why goal has not been entered and hub
will pass details to quality audit team.
12 3 Centre must investigate why goal has is not a 'SMART' goal and
hub will pass details to quality audit team.

13 1 No action needed
13 2 Speak to tutors to ensure they understand about adding
outcomes/progression notes. The hub will pass the details to the quality
audit team.
13 3 Please speak to tutors to ensure they understand the importance
of adding Tutor support notes. Funding may be deducted as a result of
this.
13 4 Please speak to tutors to ensure they understand the importance
of adding Tutor support notes and about adding outcome / progression
notes.
Funding may be deducted as a result of this.
13 5 Please speak to tutors to ensure they understand the importance
of adding good Tutor support notes and about adding good outcome /
progression notes. Funding may be deducted as a result of this.

14 1 No action needed
14 2 Please look at learner's notes and encourage them to be more
course specific with tutor support or to log more.
14 3 Please encourage learners to log tutor support and also log
this
at a tutor level. Funding may be deducted as a result of this.

15 1 No action needed
15 2 Please get learner to tick their outcomes otherwise funding
will
not be automatically triggered.

16 1 No action needed
16 2 Please get tutor to confirm all learners ticked outcomes,
otherwise funding will not be automatically triggered.
16 3 Please get learner to tick outcomes ASAP! Funding may be
deducted as a result of this. This may also be passed to the Hubs Quality
Audit Team.

17 1 No action needed
17 2 No action needed
17 3 No action needed
17 4 Please check to see if learner has achieved their goal, if so
get
them to tick their box, if the learner has not achieved then there is no
action required.

18 1 No action needed
18 2 Please get the tutor to confirm the learners ticked goal.
Achievement funding may be deducted as a result of this. This may also be
passed to the Hubs Quality Audit Team.
18 3 Please get learner to tick Goal as the achievement funding may
be
deducted as a result of this. This may also be passed to the Hubs Quality
Audit Team.

19 1 No action needed
19 2 Please send the learners ILP in to the HUB ASAP!



"Tom Ogilvy" wrote in message
...
Put up the code you have written/how you are using vlookup.

--
Regards,
Tom Ogilvy


"Peter Bailey" wrote in message
...
I have in the spreadsheet:

the columns contain descriptions of events and each event can have an
error
code attached.
Log_off Fill in form Section blank
row 1 2 4 5

the errorcode table on another sheet

Log_off 1 logged off correctly no action
Log_off 2 logged off incorectly teach how to log off

Fill in form 1 Filled in correctly no action
Fill in form 2 filled form incorectly teach how to fill
in
form

How do I do the vlookup in this case as if I just used the numerical
column
it wouldnt know which 1 of 1 or 2 of 2 to return.

Logically it wants to lookup the first column ie Vlookup description of
error and then vlookup the number within that desription.


Hope that makes sense.

Regards
Peter