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:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.