In an excel file,
Change Sheet1 Name as Report and type these:
Sr. Center Name Course Date of Enrol Code
1 Alma Computer Training Institute Suraj basic computer Course 31/12/13
2 Center3 Name1 Course 5 1/1/2014
3 Center5 Name2 Course 3 1/4/2014
4 Center2 Name3 Course 4 1/6/2014
5 Center4 Name4 Course 2 1/7/2014
========= ======
Change Sheet2 Name as CourseCodes and type these:
Titles of Courses Course Codes
basic computer Course CC1
Course 2 CC2
Course 3 CC3
Course 4 CC4
Course 5 CC5
============ =========
Change Sheet3 Name as CenterCodes and type these:
Names of Courses Center Codes
Alma Computer Training Institute ACT1
Center2 ACT2
Center3 ACT3
Center4 ACT4
Center5 ACT5
============= =====
Now, Click Alt-F11.
VB Editor will open.
Insert Userform1.
Add a command Button Control to this form from the tool box.
Rename it as cmdGenerateStudentCodes.
(If you want more guidelines on opening and using VB Editor, refer the Net).
=========== =========
Now copy this code in Userform1.
[code]
Private Sub cmdGenerateStudentCodes_Click()
ThisWorkbook.Activate
Sheets("Report").Select 'Sheet1
Range("c2").Select
Dim LstRow As Long
Dim RowCtr As Long
Dim CrsTitle As Range
Dim CntrNam As Range
Dim srchCrsRange As Range
Dim srchCenterRange As Range
Dim FoundCourseTitle As String
Dim FoundCenterName As String
Dim StuCounter As Long
Dim StuNewNum As String
Dim StuNewCod As String
LstRow = Sheets("Report").Range("C" & Rows.Count).End(xlUp).Row 'to go
to last data cell inspite of blank cells. Counts Last Row that has data in
this column.
RowCtr = 1
Do
RowCtr = RowCtr + 1 'Row is incremented
Set CrsTitle = Sheets("Report").Cells(RowCtr, 4) 'RowCtr is Row. value
in Row col 4 to find. Col 4 has Course Title.
Set srchCrsRange = ThisWorkbook.Sheets(2).Range("A:B") 'Sheet2 has
Course Title in ColA and Course Code in ColB
FoundCourseTitle = Application.VLookup(CrsTitle, srchCrsRange, 2,
False) 'Col A is Col 1 in this range. So Col B is 2.
Debug.Print "FoundCourseTitle = " & FoundCourseTitle
Set CntrNam = Sheets("Report").Cells(RowCtr, 2) 'RowCtr is Row. value
in Row col 2 to find. Col 2 has Center Name.
Set srchCenterRange = ThisWorkbook.Sheets(3).Range("A:B") 'Sheet3 has
Center Name in ColA and Center Code in ColB
FoundCenterName = Application.VLookup(CntrNam, srchCenterRange, 2,
False) 'Col A is Col 1 in this range. So Col B is 2.
Debug.Print "FoundCenterName = " & FoundCenterName
Sheets("Report").Select
Range("c" & Trim(Str(RowCtr))).Select
Debug.Print "Mon = " & Month(Selection.Offset(0, 2))
Debug.Print "Day = " & Day(Selection.Offset(0, 2))
StuCounter = RowCtr - 1
Debug.Print "StuCounter = " & StuCounter
StuNewNum = Trim(Str(StuCounter))
Do While Len(StuNewNum) < 6
StuNewNum = "0" & StuNewNum
Loop
Debug.Print "StuNewNum = " & StuNewNum
StuNewCod = FoundCourseTitle & "/" & FoundCenterName & _
"/" & Month(Selection.Offset(0, 2)) & "/" & Day(Selection.Offset(0,
2)) & "/" & StuNewNum
Debug.Print "StuNewCod = " & StuNewCod
Selection.Offset(0, 3) = StuNewCod
Loop While RowCtr < LstRow 'will stop if RowCtr > LstRow
MsgBox "Report prepared!"
End Sub
[/code]
Clik the Userform name on the left panel on top portion.
The Code will be hidden and the form will appear.
Click on the tiny triangle on top or close the file, save and open again.
Run the code. Hope this helps.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.
