Solved: Using IF statement and Select Case statement

[expfresocspy]

Hi !
This is my first time working on macro excel and alot of help is needed .
I want to refer data from column D and insert another data into column E and I use this code below :-


[vba]If Columns("D").Value = "A" Then
Columns("E").Value = "Yes"
ElseIf Columns("D").Value = "B" Then
Columns("E").Value = "Yes"
ElseIf Columns("D").Value = "C" Then
Columns("E").Value = "Yes"
ElseIf Columns("D").Value = "D" Then
Columns("E").Value = "Yes"
Else: Columns("E").Value = "No"
End If
[/vba]
But the error I got was run-time error '13': Type mismatch.
So I tried another method which is this:-


[VBA]Select Case Columns("D").Value
Case "A", "B","C", "D"
Columns("E").Value = "Yes"
Case Else
Columns("E").Value = "No"
End Select

[/VBA]
But I still get the same error . Please help me ):

Thankyouuuu !

[Aflatoon]

You cannot refer to an entire column's value like that. You need to loop through the cells in column D and then perform your checks as above and amend the adjacent cell in column E.

[Paul_Hossler]

Many ways to do it. This is just one

[VBA]
Option Explicit
Sub test()
Dim rData As Range, rCell As Range


'set a range variable to only cells in D that have text constants
Set rData = Nothing
On Error Resume Next
Set rData = ActiveSheet.Columns("D").SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

If rData Is Nothing Then Exit Sub

For Each rCell In rData.Cells
Select Case rCell.Value
Case "A" To "D"
rCell.Offset(0, 1).Value = "Yes"
Case Else
rCell.Offset(0, 1).Value = "No"
End Select
Next
End Sub
[/VBA]

Paul

[expfresocspy]

Many ways to do it. This is just one

[vba]
Option Explicit
Sub test()
Dim rData As Range, rCell As Range


'set a range variable to only cells in D that have text constants
Set rData = Nothing
On Error Resume Next
Set rData = ActiveSheet.Columns("D").SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

If rData Is Nothing Then Exit Sub

For Each rCell In rData.Cells
Select Case rCell.Value
Case "A" To "D"
rCell.Offset(0, 1).Value = "Yes"
Case Else
rCell.Offset(0, 1).Value = "No"
End Select
Next
End Sub
[/vba]

Paul

Hi Paul !

Thankyou so veryvery much for your codes . It helped me alot but I still wanna learn more about VBA from experts like you as it is very interesting Do you mind explaining to me how the numbers in this code rCell.Offset(0, 1) and SpecialCells(xlCellTypeConstants, xlTextValues) work ?

I hope I'm not troubling you and/or taking your precious time >.<

Once again , thankyouu !

[expfresocspy]

You cannot refer to an entire column's value like that. You need to loop through the cells in column D and then perform your checks as above and amend the adjacent cell in column E.

Hi Aflatoon !

Thankyou veryvery much for the suggestion Correct me if I'm wrong, what I understand is that I can use the rowcount method and the for loop to make my codes work . Thats an awesome solution to my problem (: It would be better if you could provide me with some code examples so that I can learn more and gain knowledge from experts like u

[Paul_Hossler]

Do you mind explaining to me how the numbers in this code rCell.Offset(0, 1) and SpecialCells(xlCellTypeConstants, xlTextValues) work ?

1. rCell is a range object variable containing (in this case) a single cell.

rCell.Offset (x, y).value just says from the current rCell change rows by 'x' and change columns by 'y'

So if rCell = D4, then rCell.Offset (2, 4) would be H6
So if rCell = D4, then rCell.Offset (-2, 4) would be H2
So if rCell = D4, then rCell.Offset (0, 4) would be H4


2. As Aflatoon said, you need to go through each cell in a .Column. However, if you went through all the cells in Column D, that's a lot of rows.

There are many ways to stop when you've run out of data, so this was just one

.Columns("D").SpecialCell ( ) will just select the cells in column D that meet the criteria (text constants in this case).

It will fail if there are not cells that meet the criteria, so that's the reason I like to 'wrap' it on On error and then test for Nothing

The VBA help has a good write up and examples

Paul

[shrivallabha]

This is another method which doesn't use loop.
[VBA]Public Sub IfWithoutLoop()
With Range("E2:E" & Range("D" & Rows.Count).End(xlUp).Row)
.Formula = "=IF(OR(D2=""A"",D2=""B"",D2=""C"",D2=""D""),""Yes"",""No"")"
.Value = .Value
End With
End Sub
[/VBA]

[Aflatoon]

Incidentally, you may shorten your OR formula a little since you refer to the same cell each time:
[vba].Formula = "=IF(OR(D2={""A"",""B"",""C"",""D""}),""Yes"",""No"")" [/vba]

[shrivallabha]

And I thought that was short

Thanks Aflatoon, I've learned this approach from you & mikerickson.

[expfresocspy]

1. rCell is a range object variable containing (in this case) a single cell.

rCell.Offset (x, y).value just says from the current rCell change rows by 'x' and change columns by 'y'

So if rCell = D4, then rCell.Offset (2, 4) would be H6
So if rCell = D4, then rCell.Offset (-2, 4) would be H2
So if rCell = D4, then rCell.Offset (0, 4) would be H4


2. As Aflatoon said, you need to go through each cell in a .Column. However, if you went through all the cells in Column D, that's a lot of rows.

There are many ways to stop when you've run out of data, so this was just one

.Columns("D").SpecialCell ( ) will just select the cells in column D that meet the criteria (text constants in this case).

It will fail if there are not cells that meet the criteria, so that's the reason I like to 'wrap' it on On error and then test for Nothing

The VBA help has a good write up and examples

Paul

Ouuuhhhhh :O Thankyou for the explaination . I still have some diffculties in this loop area but I hope I can get through this with your help (: Thanks again !

[expfresocspy]

This is another method which doesn't use loop.
[vba]Public Sub IfWithoutLoop()
With Range("E2:E" & Range("D" & Rows.Count).End(xlUp).Row)
.Formula = "=IF(OR(D2=""A"",D2=""B"",D2=""C"",D2=""D""),""Yes"",""No"")"
.Value = .Value
End With
End Sub
[/vba]

Wow ! There's so many ways to do this O.O Thankyou for the help ! So if I were to use this method , is it possible to be used in this way ? :-

[vba]Public Sub invalidTime()
With Range("E2:E" & Range("D" & Rows.Count).End(xlUp).Row)
.Formula = "=IF(OR(D2=""between #1 Jan 1990# And #31 Dec 2009#""),""Yes"",""No"")"
.Value = .Value
End With
End Sub
[/vba]

Or am I doing it allllll wrong ? Omg , so sorry if I'm troubling you .

[expfresocspy]

Incidentally, you may shorten your OR formula a little since you refer to the same cell each time:
[vba].Formula = "=IF(OR(D2={""A"",""B"",""C"",""D""}),""Yes"",""No"")" [/vba]

Thankyou very much for the example ! Appreciate it loads . but may I know if this is possible ?

[vba].Formula = "=IF(OR(D2={""between #1 Jan 1990# And #31 Dec 2009#""}),""Yes"",""No"")" [/vba]

Thanks again ! ^^ and sorry if I'm troubling you .

[shrivallabha]

Thankyou very much for the example ! Appreciate it loads . but may I know if this is possible ?

[vba].Formula = "=IF(OR(D2={""between #1 Jan 1990# And #31 Dec 2009#""}),""Yes"",""No"")" [/vba]
Thanks again ! ^^ and sorry if I'm troubling you .

In this case, you will have to use AND and not OR like:
[VBA]Public Sub InvalidTime()
With Range("E2:E" & Range("D" & Rows.Count).End(xlUp).Row)
.Formula = "=IF(AND(D2>DATE(1900,1,1),D2<DATE(2009,12,31)),""Yes"",""No"")"
.Value = .Value
End With
End Sub[/VBA]

[expfresocspy]

In this case, you will have to use AND and not OR like:
[vba]Public Sub InvalidTime()
With Range("E2:E" & Range("D" & Rows.Count).End(xlUp).Row)
.Formula = "=IF(AND(D2>DATE(1900,1,1),D2<DATE(2009,12,31)),""Yes"",""No"")"
.Value = .Value
End With
End Sub[/vba]

YEAHHH ! It works perfectly fine now
thanks for the help