Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to use select case
Hi,
Pls see my example of the macro that i did. My intention is to find in column G, cells that has either values of "AS", "HC" or "50", if found , then for column P in the same row, it will return that value. But it does not work. i tried to use selection.find but i cannot multi find, so i use select...case instead. Can anyone advise my mistake in my macro below? e.g. Sub Testing() Dim Result As String Dim CheckVal As String Range("A1").Select NumOfRows = Cells(Rows.Count, "1").End(xlUp).Row CheckVal = ActiveSheet.Cells(CurrentRow, 7) 'Column G Result = ActiveSheet.Cells(CurrentRow, 16) 'Column P For CurrentRow = 1 To NumOfRows Select Case UCase(CheckVal) Case "AS" Result = "AS" Case "50" Result = "50" Case "HC" Result = "HC" Case Else Result = "" 'equals to blank End Select Next MsgBox ("Done!!!Thanks for Playing!!!") End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to use select case
Hi Junior,
Perhaps try something like: '============= Public Sub Testing() Dim CheckVal As String Dim NumOfRows As Long Dim CurrentRow As Long NumOfRows = Cells(Rows.Count, 1).End(xlUp).Row For CurrentRow = 1 To NumOfRows CheckVal = ActiveSheet.Cells(CurrentRow, 7).Value With ActiveSheet.Cells(CurrentRow, 7) Select Case UCase(CheckVal) Case "AS", "SO", "HC" 'Do nothing Case Else .Value = "" End Select End With Next CurrentRow MsgBox ("Done!!!Thanks for Playing!!!") End Sub '<<============= --- Regards, Norman "Junior728" wrote in message ... Hi, Pls see my example of the macro that i did. My intention is to find in column G, cells that has either values of "AS", "HC" or "50", if found , then for column P in the same row, it will return that value. But it does not work. i tried to use selection.find but i cannot multi find, so i use select...case instead. Can anyone advise my mistake in my macro below? e.g. Sub Testing() Dim Result As String Dim CheckVal As String Range("A1").Select NumOfRows = Cells(Rows.Count, "1").End(xlUp).Row CheckVal = ActiveSheet.Cells(CurrentRow, 7) 'Column G Result = ActiveSheet.Cells(CurrentRow, 16) 'Column P For CurrentRow = 1 To NumOfRows Select Case UCase(CheckVal) Case "AS" Result = "AS" Case "50" Result = "50" Case "HC" Result = "HC" Case Else Result = "" 'equals to blank End Select Next MsgBox ("Done!!!Thanks for Playing!!!") End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to use select case
Your mistake is CheckVal and Results need to be inside the "For loop'. both
equations use CurrentRow. From: CheckVal = ActiveSheet.Cells(CurrentRow, 7) 'Column G Result = ActiveSheet.Cells(CurrentRow, 16) 'Column P For CurrentRow = 1 To NumOfRows to: For CurrentRow = 1 To NumOfRows CheckVal = ActiveSheet.Cells(CurrentRow, 7) 'Column G Result = ActiveSheet.Cells(CurrentRow, 16) 'Column P "Junior728" wrote: Hi, Pls see my example of the macro that i did. My intention is to find in column G, cells that has either values of "AS", "HC" or "50", if found , then for column P in the same row, it will return that value. But it does not work. i tried to use selection.find but i cannot multi find, so i use select...case instead. Can anyone advise my mistake in my macro below? e.g. Sub Testing() Dim Result As String Dim CheckVal As String Range("A1").Select NumOfRows = Cells(Rows.Count, "1").End(xlUp).Row CheckVal = ActiveSheet.Cells(CurrentRow, 7) 'Column G Result = ActiveSheet.Cells(CurrentRow, 16) 'Column P For CurrentRow = 1 To NumOfRows Select Case UCase(CheckVal) Case "AS" Result = "AS" Case "50" Result = "50" Case "HC" Result = "HC" Case Else Result = "" 'equals to blank End Select Next MsgBox ("Done!!!Thanks for Playing!!!") End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to use select case
Case "AS", "HC", 50: Cells(i, "h") = Cells(i, "p") should be Case "AS", "HC", 50: Cells(i, "P") = Cells(i, "G") -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... IF?? I understand what you want. Sub findtest() For i = 1 To Cells(Rows.Count, "g").End(xlUp).row Select Case UCase(Cells(i, "g")) Case "AS", "HC", 50: Cells(i, "h") = Cells(i, "p") Case Else End Select Next i End Sub -- Don Guillett SalesAid Software "Junior728" wrote in message ... Hi, Pls see my example of the macro that i did. My intention is to find in column G, cells that has either values of "AS", "HC" or "50", if found , then for column P in the same row, it will return that value. But it does not work. i tried to use selection.find but i cannot multi find, so i use select...case instead. Can anyone advise my mistake in my macro below? e.g. Sub Testing() Dim Result As String Dim CheckVal As String Range("A1").Select NumOfRows = Cells(Rows.Count, "1").End(xlUp).Row CheckVal = ActiveSheet.Cells(CurrentRow, 7) 'Column G Result = ActiveSheet.Cells(CurrentRow, 16) 'Column P For CurrentRow = 1 To NumOfRows Select Case UCase(CheckVal) Case "AS" Result = "AS" Case "50" Result = "50" Case "HC" Result = "HC" Case Else Result = "" 'equals to blank End Select Next MsgBox ("Done!!!Thanks for Playing!!!") End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to use select case
Hi All,
It works perfectly...thank you all...=) "Junior728" wrote: Hi, Pls see my example of the macro that i did. My intention is to find in column G, cells that has either values of "AS", "HC" or "50", if found , then for column P in the same row, it will return that value. But it does not work. i tried to use selection.find but i cannot multi find, so i use select...case instead. Can anyone advise my mistake in my macro below? e.g. Sub Testing() Dim Result As String Dim CheckVal As String Range("A1").Select NumOfRows = Cells(Rows.Count, "1").End(xlUp).Row CheckVal = ActiveSheet.Cells(CurrentRow, 7) 'Column G Result = ActiveSheet.Cells(CurrentRow, 16) 'Column P For CurrentRow = 1 To NumOfRows Select Case UCase(CheckVal) Case "AS" Result = "AS" Case "50" Result = "50" Case "HC" Result = "HC" Case Else Result = "" 'equals to blank End Select Next MsgBox ("Done!!!Thanks for Playing!!!") End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
select case in vba | Excel Discussion (Misc queries) | |||
Case without Select Case error problem | Excel Discussion (Misc queries) | |||
End Select without Select Case, Block If without End If errors | Excel Programming | |||
For Each with Select Case | Excel Programming |