PDA

View Full Version : Solved: Using IF statement and Select Case statement



expfresocspy
12-22-2011, 02:10 AM
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 :-


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

But the error I got was run-time error '13': Type mismatch.
So I tried another method which is this:-


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


But I still get the same error . Please help me ):

Thankyouuuu !

Aflatoon
12-22-2011, 03:27 AM
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
12-22-2011, 12:57 PM
Many ways to do it. This is just one


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: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


Paul

expfresocspy
12-22-2011, 07:31 PM
Many ways to do it. This is just one


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: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


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 :D 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
12-22-2011, 07:36 PM
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 :D 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 :bow:

Paul_Hossler
12-23-2011, 05:43 AM
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: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
12-23-2011, 10:05 AM
This is another method which doesn't use loop.
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

Aflatoon
12-26-2011, 03:33 AM
Incidentally, you may shorten your OR formula a little since you refer to the same cell each time:
.Formula = "=IF(OR(D2={""A"",""B"",""C"",""D""}),""Yes"",""No"")"

shrivallabha
12-26-2011, 06:51 AM
And I thought that was short:rotlaugh:

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

expfresocspy
12-26-2011, 08:19 PM
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: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
12-26-2011, 08:25 PM
This is another method which doesn't use loop.
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


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 ? :-

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


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

expfresocspy
12-26-2011, 08:27 PM
Incidentally, you may shorten your OR formula a little since you refer to the same cell each time:
.Formula = "=IF(OR(D2={""A"",""B"",""C"",""D""}),""Yes"",""No"")"

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

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

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

shrivallabha
12-27-2011, 06:30 AM
Thankyou very much for the example ! :D Appreciate it loads . but may I know if this is possible ?

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

In this case, you will have to use AND and not OR like:
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

expfresocspy
01-04-2012, 07:50 PM
In this case, you will have to use AND and not OR like:
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

YEAHHH ! It works perfectly fine now :D
thanks for the help :friends: :bow: