#1   Report Post  
Posted to microsoft.public.excel.misc
George
 
Posts: n/a
Default Select Case - issue

Hi,
I am trying to search through Column A for group name and match this with
persons name in col B then output a value in column C. Here is my macro thus
far. Note same person can appear in both groups - help needed & appreciated

Sub Group_Locate()

Dim Counter As Integer
Dim strFund As String

Counter = 1
strFund = Cells(Counter, 1).Value

While strFund < Empty And Counter < 5000
Select Case strFund

Case "GroupA" And Cells(Counter, Counter + 1) = "Terence Darby":
Cells(Counter, Counter + 3).Value = 15
'Case "GroupB" And Cells(Counter, Counter + 1) = "Jonny Butler":
Cells(Counter, Counter + 3).Value = 22
'Case "GroupA" And Cells(Counter, Counter + 1) = "Jonny Butler":
Cells(Counter, Counter + 3).Value = 61
Case Else:
End Select
Counter = Counter + 1
strFund = Cells(Counter, 4).Value
Wend

End Sub

E.g run macro over belowData:
Cell A1 = GroupA
Cell B1 = Terence Darby
Cell A2 = Group A
Cell B2 = Jonny Butler

Result in Col C1 = 15
Result in Col C2 = 61

Thanks
George
  #2   Report Post  
Posted to microsoft.public.excel.misc
Toppers
 
Posts: n/a
Default Select Case - issue

If there are many selections in Column B and/or many Groups), then this is
probably not a good solution as you will have many IF (Case)statements.

VLOOKUP may be a better alternative if the above is true.

Sub Group_Locate()

Dim Counter As Integer
Dim strFund As String

Counter = 1
strFund = Cells(Counter, 1).Value

While strFund < "" And Counter < 5000
Select Case strFund

Case "GroupA"
If Cells(Counter, 2) = "Terence Darby" Then
Cells(Counter, 3).Value = 15
Else
If Cells(Counter, 2) = "Jonny Butler" Then
Cells(Counter, 3).Value = 61
End If
End If
Case "GroupB"
If Cells(Counter, 2) = "Jonny Butler" Then
Cells(Counter, 3).Value = 22
End If
End Select
Counter = Counter + 1
strFund = Cells(Counter, 1).Value
Wend

End Sub

"George" wrote:

Hi,
I am trying to search through Column A for group name and match this with
persons name in col B then output a value in column C. Here is my macro thus
far. Note same person can appear in both groups - help needed & appreciated

Sub Group_Locate()

Dim Counter As Integer
Dim strFund As String

Counter = 1
strFund = Cells(Counter, 1).Value

While strFund < Empty And Counter < 5000
Select Case strFund

Case "GroupA" And Cells(Counter, Counter + 1) = "Terence Darby":
Cells(Counter, Counter + 3).Value = 15
'Case "GroupB" And Cells(Counter, Counter + 1) = "Jonny Butler":
Cells(Counter, Counter + 3).Value = 22
'Case "GroupA" And Cells(Counter, Counter + 1) = "Jonny Butler":
Cells(Counter, Counter + 3).Value = 61
Case Else:
End Select
Counter = Counter + 1
strFund = Cells(Counter, 4).Value
Wend

End Sub

E.g run macro over belowData:
Cell A1 = GroupA
Cell B1 = Terence Darby
Cell A2 = Group A
Cell B2 = Jonny Butler

Result in Col C1 = 15
Result in Col C2 = 61

Thanks
George

  #3   Report Post  
Posted to microsoft.public.excel.misc
George
 
Posts: n/a
Default Select Case - issue

Thanks Toppers,
yes there are many groups & 200 names.
Can you suggest a lookup that can do what i am proposing below?
Thanks
George

"Toppers" wrote:

If there are many selections in Column B and/or many Groups), then this is
probably not a good solution as you will have many IF (Case)statements.

VLOOKUP may be a better alternative if the above is true.

Sub Group_Locate()

Dim Counter As Integer
Dim strFund As String

Counter = 1
strFund = Cells(Counter, 1).Value

While strFund < "" And Counter < 5000
Select Case strFund

Case "GroupA"
If Cells(Counter, 2) = "Terence Darby" Then
Cells(Counter, 3).Value = 15
Else
If Cells(Counter, 2) = "Jonny Butler" Then
Cells(Counter, 3).Value = 61
End If
End If
Case "GroupB"
If Cells(Counter, 2) = "Jonny Butler" Then
Cells(Counter, 3).Value = 22
End If
End Select
Counter = Counter + 1
strFund = Cells(Counter, 1).Value
Wend

End Sub

"George" wrote:

Hi,
I am trying to search through Column A for group name and match this with
persons name in col B then output a value in column C. Here is my macro thus
far. Note same person can appear in both groups - help needed & appreciated

Sub Group_Locate()

Dim Counter As Integer
Dim strFund As String

Counter = 1
strFund = Cells(Counter, 1).Value

While strFund < Empty And Counter < 5000
Select Case strFund

Case "GroupA" And Cells(Counter, Counter + 1) = "Terence Darby":
Cells(Counter, Counter + 3).Value = 15
'Case "GroupB" And Cells(Counter, Counter + 1) = "Jonny Butler":
Cells(Counter, Counter + 3).Value = 22
'Case "GroupA" And Cells(Counter, Counter + 1) = "Jonny Butler":
Cells(Counter, Counter + 3).Value = 61
Case Else:
End Select
Counter = Counter + 1
strFund = Cells(Counter, 4).Value
Wend

End Sub

E.g run macro over belowData:
Cell A1 = GroupA
Cell B1 = Terence Darby
Cell A2 = Group A
Cell B2 = Jonny Butler

Result in Col C1 = 15
Result in Col C2 = 61

Thanks
George

  #4   Report Post  
Posted to microsoft.public.excel.misc
Toppers
 
Posts: n/a
Default Select Case - issue

George,

One possible solution. This assumes a table in Sheet2 in colums A-C
containing Group, Name and Value sorted by Group.

The function finds the Group (first record of) and then searches the names
in that group to find the value.

If a Group or Name isn't found, the function returns a value of 0.

HTH

Function GetValue(ByVal Group As String, ByVal Name As String) As Integer

Dim rnga As Range
Dim lastrow As Long, n As Integer
Dim row, code

With Worksheets("Sheet2") '<=== change as required
lastrow = .Cells(Rows.Count, "A").End(xlUp).row
Set rnga = .Range("A1:A" & lastrow)
row = Application.Match(Group, rnga, 0) ' first record forthis group ....
If IsError(row) Then
MsgBox Group & " was not found"
GetValue = 0
Exit Function
Else
' look for value for this name ........
n = Application.CountIf(rnga, Group) ' number of records in this group
code = Application.VLookup(Name, .Range(.Cells(row, "B"), .Cells(row
+ n - 1, "C")), 2, False)
If IsError(code) Then
MsgBox Name & " was not found"
GetValue = 0
Exit Function
End If
End If
End With
GetValue = code
End Function


Sub Group_Locate()

Dim Counter As Integer
Dim strFund As String

Counter = 1

With Worksheets("Sheet1") <=== change as required
While .Cells(Counter, "A") < "" Or Counter < 5000
.Cells(Counter, 3) = GetValue(.Cell(Counter, "A"), .Cells(Counter, "B"))
Counter = Counter + 1
Wend
End With


End Sub

"George" wrote:

Hi,
I am trying to search through Column A for group name and match this with
persons name in col B then output a value in column C. Here is my macro thus
far. Note same person can appear in both groups - help needed & appreciated

Sub Group_Locate()

Dim Counter As Integer
Dim strFund As String

Counter = 1
strFund = Cells(Counter, 1).Value

While strFund < Empty And Counter < 5000
Select Case strFund

Case "GroupA" And Cells(Counter, Counter + 1) = "Terence Darby":
Cells(Counter, Counter + 3).Value = 15
'Case "GroupB" And Cells(Counter, Counter + 1) = "Jonny Butler":
Cells(Counter, Counter + 3).Value = 22
'Case "GroupA" And Cells(Counter, Counter + 1) = "Jonny Butler":
Cells(Counter, Counter + 3).Value = 61
Case Else:
End Select
Counter = Counter + 1
strFund = Cells(Counter, 4).Value
Wend

End Sub

E.g run macro over belowData:
Cell A1 = GroupA
Cell B1 = Terence Darby
Cell A2 = Group A
Cell B2 = Jonny Butler

Result in Col C1 = 15
Result in Col C2 = 61

Thanks
George

  #5   Report Post  
Posted to microsoft.public.excel.misc
George
 
Posts: n/a
Default Select Case - issue

Thanks very much Toppers,
One question:
Is the function run on data in sheet 1 then searches sheet 2 for matches?
Cheers

"Toppers" wrote:

George,

One possible solution. This assumes a table in Sheet2 in colums A-C
containing Group, Name and Value sorted by Group.

The function finds the Group (first record of) and then searches the names
in that group to find the value.

If a Group or Name isn't found, the function returns a value of 0.

HTH

Function GetValue(ByVal Group As String, ByVal Name As String) As Integer

Dim rnga As Range
Dim lastrow As Long, n As Integer
Dim row, code

With Worksheets("Sheet2") '<=== change as required
lastrow = .Cells(Rows.Count, "A").End(xlUp).row
Set rnga = .Range("A1:A" & lastrow)
row = Application.Match(Group, rnga, 0) ' first record forthis group ....
If IsError(row) Then
MsgBox Group & " was not found"
GetValue = 0
Exit Function
Else
' look for value for this name ........
n = Application.CountIf(rnga, Group) ' number of records in this group
code = Application.VLookup(Name, .Range(.Cells(row, "B"), .Cells(row
+ n - 1, "C")), 2, False)
If IsError(code) Then
MsgBox Name & " was not found"
GetValue = 0
Exit Function
End If
End If
End With
GetValue = code
End Function


Sub Group_Locate()

Dim Counter As Integer
Dim strFund As String

Counter = 1

With Worksheets("Sheet1") <=== change as required
While .Cells(Counter, "A") < "" Or Counter < 5000
.Cells(Counter, 3) = GetValue(.Cell(Counter, "A"), .Cells(Counter, "B"))
Counter = Counter + 1
Wend
End With


End Sub

"George" wrote:

Hi,
I am trying to search through Column A for group name and match this with
persons name in col B then output a value in column C. Here is my macro thus
far. Note same person can appear in both groups - help needed & appreciated

Sub Group_Locate()

Dim Counter As Integer
Dim strFund As String

Counter = 1
strFund = Cells(Counter, 1).Value

While strFund < Empty And Counter < 5000
Select Case strFund

Case "GroupA" And Cells(Counter, Counter + 1) = "Terence Darby":
Cells(Counter, Counter + 3).Value = 15
'Case "GroupB" And Cells(Counter, Counter + 1) = "Jonny Butler":
Cells(Counter, Counter + 3).Value = 22
'Case "GroupA" And Cells(Counter, Counter + 1) = "Jonny Butler":
Cells(Counter, Counter + 3).Value = 61
Case Else:
End Select
Counter = Counter + 1
strFund = Cells(Counter, 4).Value
Wend

End Sub

E.g run macro over belowData:
Cell A1 = GroupA
Cell B1 = Terence Darby
Cell A2 = Group A
Cell B2 = Jonny Butler

Result in Col C1 = 15
Result in Col C2 = 61

Thanks
George



  #6   Report Post  
Posted to microsoft.public.excel.misc
Toppers
 
Posts: n/a
Default Select Case - issue

George,
The function (called from the loop in Group_Locate module )
has input from "Sheet1" i.e Group and Name, and looks at the table (Group,
Name and Code) in "Sheet2" to get the Code (Numeric value).

The macros themselves should be placed in a general module.

HTH

"George" wrote:

Thanks very much Toppers,
One question:
Is the function run on data in sheet 1 then searches sheet 2 for matches?
Cheers

"Toppers" wrote:

George,

One possible solution. This assumes a table in Sheet2 in colums A-C
containing Group, Name and Value sorted by Group.

The function finds the Group (first record of) and then searches the names
in that group to find the value.

If a Group or Name isn't found, the function returns a value of 0.

HTH

Function GetValue(ByVal Group As String, ByVal Name As String) As Integer

Dim rnga As Range
Dim lastrow As Long, n As Integer
Dim row, code

With Worksheets("Sheet2") '<=== change as required
lastrow = .Cells(Rows.Count, "A").End(xlUp).row
Set rnga = .Range("A1:A" & lastrow)
row = Application.Match(Group, rnga, 0) ' first record forthis group ....
If IsError(row) Then
MsgBox Group & " was not found"
GetValue = 0
Exit Function
Else
' look for value for this name ........
n = Application.CountIf(rnga, Group) ' number of records in this group
code = Application.VLookup(Name, .Range(.Cells(row, "B"), .Cells(row
+ n - 1, "C")), 2, False)
If IsError(code) Then
MsgBox Name & " was not found"
GetValue = 0
Exit Function
End If
End If
End With
GetValue = code
End Function


Sub Group_Locate()

Dim Counter As Integer
Dim strFund As String

Counter = 1

With Worksheets("Sheet1") <=== change as required
While .Cells(Counter, "A") < "" Or Counter < 5000
.Cells(Counter, 3) = GetValue(.Cell(Counter, "A"), .Cells(Counter, "B"))
Counter = Counter + 1
Wend
End With


End Sub

"George" wrote:

Hi,
I am trying to search through Column A for group name and match this with
persons name in col B then output a value in column C. Here is my macro thus
far. Note same person can appear in both groups - help needed & appreciated

Sub Group_Locate()

Dim Counter As Integer
Dim strFund As String

Counter = 1
strFund = Cells(Counter, 1).Value

While strFund < Empty And Counter < 5000
Select Case strFund

Case "GroupA" And Cells(Counter, Counter + 1) = "Terence Darby":
Cells(Counter, Counter + 3).Value = 15
'Case "GroupB" And Cells(Counter, Counter + 1) = "Jonny Butler":
Cells(Counter, Counter + 3).Value = 22
'Case "GroupA" And Cells(Counter, Counter + 1) = "Jonny Butler":
Cells(Counter, Counter + 3).Value = 61
Case Else:
End Select
Counter = Counter + 1
strFund = Cells(Counter, 4).Value
Wend

End Sub

E.g run macro over belowData:
Cell A1 = GroupA
Cell B1 = Terence Darby
Cell A2 = Group A
Cell B2 = Jonny Butler

Result in Col C1 = 15
Result in Col C2 = 61

Thanks
George

  #7   Report Post  
Posted to microsoft.public.excel.misc
George
 
Posts: n/a
Default Select Case - issue

Hey Toppers
I am sorry to be totally dependent on your help but i am having trouble and
am not sure how to set out the code in the module.
I have tried naming the macro Group_Locate and putting The function under
that but it is not working. I am a novice and am not sure where to put the
function code.
George

"Toppers" wrote:

George,
The function (called from the loop in Group_Locate module )
has input from "Sheet1" i.e Group and Name, and looks at the table (Group,
Name and Code) in "Sheet2" to get the Code (Numeric value).

The macros themselves should be placed in a general module.

HTH

"George" wrote:

Thanks very much Toppers,
One question:
Is the function run on data in sheet 1 then searches sheet 2 for matches?
Cheers

"Toppers" wrote:

George,

One possible solution. This assumes a table in Sheet2 in colums A-C
containing Group, Name and Value sorted by Group.

The function finds the Group (first record of) and then searches the names
in that group to find the value.

If a Group or Name isn't found, the function returns a value of 0.

HTH

Function GetValue(ByVal Group As String, ByVal Name As String) As Integer

Dim rnga As Range
Dim lastrow As Long, n As Integer
Dim row, code

With Worksheets("Sheet2") '<=== change as required
lastrow = .Cells(Rows.Count, "A").End(xlUp).row
Set rnga = .Range("A1:A" & lastrow)
row = Application.Match(Group, rnga, 0) ' first record forthis group ....
If IsError(row) Then
MsgBox Group & " was not found"
GetValue = 0
Exit Function
Else
' look for value for this name ........
n = Application.CountIf(rnga, Group) ' number of records in this group
code = Application.VLookup(Name, .Range(.Cells(row, "B"), .Cells(row
+ n - 1, "C")), 2, False)
If IsError(code) Then
MsgBox Name & " was not found"
GetValue = 0
Exit Function
End If
End If
End With
GetValue = code
End Function


Sub Group_Locate()

Dim Counter As Integer
Dim strFund As String

Counter = 1

With Worksheets("Sheet1") <=== change as required
While .Cells(Counter, "A") < "" Or Counter < 5000
.Cells(Counter, 3) = GetValue(.Cell(Counter, "A"), .Cells(Counter, "B"))
Counter = Counter + 1
Wend
End With


End Sub

"George" wrote:

Hi,
I am trying to search through Column A for group name and match this with
persons name in col B then output a value in column C. Here is my macro thus
far. Note same person can appear in both groups - help needed & appreciated

Sub Group_Locate()

Dim Counter As Integer
Dim strFund As String

Counter = 1
strFund = Cells(Counter, 1).Value

While strFund < Empty And Counter < 5000
Select Case strFund

Case "GroupA" And Cells(Counter, Counter + 1) = "Terence Darby":
Cells(Counter, Counter + 3).Value = 15
'Case "GroupB" And Cells(Counter, Counter + 1) = "Jonny Butler":
Cells(Counter, Counter + 3).Value = 22
'Case "GroupA" And Cells(Counter, Counter + 1) = "Jonny Butler":
Cells(Counter, Counter + 3).Value = 61
Case Else:
End Select
Counter = Counter + 1
strFund = Cells(Counter, 4).Value
Wend

End Sub

E.g run macro over belowData:
Cell A1 = GroupA
Cell B1 = Terence Darby
Cell A2 = Group A
Cell B2 = Jonny Butler

Result in Col C1 = 15
Result in Col C2 = 61

Thanks
George

  #8   Report Post  
Posted to microsoft.public.excel.misc
Toppers
 
Posts: n/a
Default Select Case - issue

George,
Send me the workbook and I'll insert the code
).

OR

Open your workbook and press ALT+F11 which will open the VISUAL BASIC EDITOR
(VBE). On the left-hand side you should the Project panel and the last entry
will be your worbook (VBAProject ( <bookname) with a list of your sheets.
Right-click on any of these and do INSERT==Module: a new entry "Module 1"
will be created. Copy and paste all the code I sent into this (blank area of
screen on your right).

Put your cursor somewhere in the "Group_Locate" code and click the green
"arrow head" (run sub/userform") on the (usually) first toolbar in the VBE.
this will execute the macro.

Be sure you have set up the table on Sheet2 (or your equivalent).

HTH

"George" wrote:

Hey Toppers
I am sorry to be totally dependent on your help but i am having trouble and
am not sure how to set out the code in the module.
I have tried naming the macro Group_Locate and putting The function under
that but it is not working. I am a novice and am not sure where to put the
function code.
George

"Toppers" wrote:

George,
The function (called from the loop in Group_Locate module )
has input from "Sheet1" i.e Group and Name, and looks at the table (Group,
Name and Code) in "Sheet2" to get the Code (Numeric value).

The macros themselves should be placed in a general module.

HTH

"George" wrote:

Thanks very much Toppers,
One question:
Is the function run on data in sheet 1 then searches sheet 2 for matches?
Cheers

"Toppers" wrote:

George,

One possible solution. This assumes a table in Sheet2 in colums A-C
containing Group, Name and Value sorted by Group.

The function finds the Group (first record of) and then searches the names
in that group to find the value.

If a Group or Name isn't found, the function returns a value of 0.

HTH

Function GetValue(ByVal Group As String, ByVal Name As String) As Integer

Dim rnga As Range
Dim lastrow As Long, n As Integer
Dim row, code

With Worksheets("Sheet2") '<=== change as required
lastrow = .Cells(Rows.Count, "A").End(xlUp).row
Set rnga = .Range("A1:A" & lastrow)
row = Application.Match(Group, rnga, 0) ' first record forthis group ....
If IsError(row) Then
MsgBox Group & " was not found"
GetValue = 0
Exit Function
Else
' look for value for this name ........
n = Application.CountIf(rnga, Group) ' number of records in this group
code = Application.VLookup(Name, .Range(.Cells(row, "B"), .Cells(row
+ n - 1, "C")), 2, False)
If IsError(code) Then
MsgBox Name & " was not found"
GetValue = 0
Exit Function
End If
End If
End With
GetValue = code
End Function


Sub Group_Locate()

Dim Counter As Integer
Dim strFund As String

Counter = 1

With Worksheets("Sheet1") <=== change as required
While .Cells(Counter, "A") < "" Or Counter < 5000
.Cells(Counter, 3) = GetValue(.Cell(Counter, "A"), .Cells(Counter, "B"))
Counter = Counter + 1
Wend
End With


End Sub

"George" wrote:

Hi,
I am trying to search through Column A for group name and match this with
persons name in col B then output a value in column C. Here is my macro thus
far. Note same person can appear in both groups - help needed & appreciated

Sub Group_Locate()

Dim Counter As Integer
Dim strFund As String

Counter = 1
strFund = Cells(Counter, 1).Value

While strFund < Empty And Counter < 5000
Select Case strFund

Case "GroupA" And Cells(Counter, Counter + 1) = "Terence Darby":
Cells(Counter, Counter + 3).Value = 15
'Case "GroupB" And Cells(Counter, Counter + 1) = "Jonny Butler":
Cells(Counter, Counter + 3).Value = 22
'Case "GroupA" And Cells(Counter, Counter + 1) = "Jonny Butler":
Cells(Counter, Counter + 3).Value = 61
Case Else:
End Select
Counter = Counter + 1
strFund = Cells(Counter, 4).Value
Wend

End Sub

E.g run macro over belowData:
Cell A1 = GroupA
Cell B1 = Terence Darby
Cell A2 = Group A
Cell B2 = Jonny Butler

Result in Col C1 = 15
Result in Col C2 = 61

Thanks
George

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to use spellnumber formula Aarif Excel Worksheet Functions 3 February 27th 06 05:36 PM
Currency to Text mytipi Excel Worksheet Functions 1 February 22nd 06 12:43 AM
How to .. sbgvp Excel Discussion (Misc queries) 8 October 4th 05 09:16 PM
convert value in word. For Exampe Rs.115.00 convert into word as . Shakti Excel Discussion (Misc queries) 1 May 10th 05 12:00 PM
Is there a formula to spell out a number in excel? Sha-nay-nay Excel Worksheet Functions 2 December 18th 04 10:25 PM


All times are GMT +1. The time now is 10:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"