how to use select case

[Junior728]

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

 

[Norman Jones]

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
'<<=============

 

[Joel]

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

 

[Don Guillett]

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]

Case "AS", "HC", 50: Cells(i, "h") = Cells(i, "p")
should be

Case "AS", "HC", 50: Cells(i, "P") = Cells(i, "G")

 

[Junior728]

Hi All,

It works perfectly...thank you all...=)