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

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

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

how to use select case
 

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

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

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