If then macro

Need help frnds...

I have a sheet where there are two cells: C18 and J12
C18 has a numeric value and there are 4 numeric charachters there like 4412,4416, 4417,5437 (these are numeric combinations and the combination will always be of 4 numeric charachters)

J12 contains text

what i want is that macro which checks if from C18 to C4018, if there is any numeric combination which ends with 7 then it checks if the text in cell J12 starts only with AAA or BBB or CC or DD (these will always stay the same), if it does, then it is ok, otherwise it gives a pop up message showing error.

guys plz help me with this.....

thanks heaps
i think if then and loop statement will do here, with message box

I hope this is what you are looking for:

Sub Button1_Click()

Set rng = Range("C18:C4018")

For Each cl In rng

If Not (Int(Right(cl, 1)) = 7 And (Left(Range("J12"), 3) = "AAA" Or Left(Range("J12"), 3) = "BBB" Or Left(Range("J12"), 2) = "CC" Or Left(Range("J12"), 2) = "DD")) Then

cl.Select
MsgBox cl.Address

End If

Next

End Sub


- Mangesh

Hey Mangesh, Thanks for ur help....I appreciate ur help but
This is almost what I am looking for

but what I exactly need is that it searches in cells from C18 to C4018 and if it finds a cell in which the numeric combination ends with 7 (there will be other combinations ending with 2,3 and 5 also in this list), then it checks what is written in Cell J12, if in cell J12 it has text beginning with AAA or BBB or CC or DD......then it is ok, no need of any message but if it has something else in Cell J12 then show error message



thanks

i have a macro and I want that macro should run only on the cells which are used and the cells start from C18...Like if the cells are used till C35, the macro should run only on C18 to C35.....please help me with this

thnks guys

gUYS COULD U PLZ HELP ME WITH THIS..i WILL APPRECIATE UR HELP

try this code. It allows for easier changes to the acceptable code ("AAA", etc) and the number of acceptable codes

Const No_of_Codes As Integer = 4

Sub test()

Dim Codes(No_of_Codes) As String
Dim FindError As Boolean

Codes(0) = "AAA"
Codes(1) = "BBB"
Codes(2) = "CC"
Codes(3) = "DD"


For Each c In Range("C18:C4018")
If Right(c.Value, 1) = 7 Then
FindError = True
For i = 0 To No_of_Codes - 1 Step 1
If Left(Range("J12").Value, Len(Codes(i))) = Codes(i) Then FindError = False
Next i
Exit For
End If
Next c

If FindError Then MsgBox "Error"

End Sub

Cheers!

what code does is searches for cells in range c18:c4018 for values ending with 7 , then it sees j12 for values as you defined, if all the condition are satisfied then nothing will happen otherwise error message


I used some code from Mangesh



Sub Button1_Click()

Set rng = Range("C18:C4018") 'range to searched

rng.Select 'select the range
On Error Resume Next
'find cells ending with 7
Selection.Find(What:="*7", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If Err.Description <> "" Then
MsgBox "no cells ending with 7"
Err.Clear
Else
On Error Resume Next
If Left(Range("J12"), 3) = "AAA" Or Left(Range("J12"), 3) = "BBB" Or Left(Range("J12"), 2) = "CC" Or Left(Range("J12"), 2) = "DD" Then
If Err.Description <> "" Then
Err.Clear
MsgBox "j12 not in correct format"
GoTo a:
Else

End If

Else
MsgBox "j12 not in correct format"
End If
End If
a:
End Sub

Thank u so much frnd..I really am thankful to u

can we adjust it a bit..like the range of cells in column C is volatile...here in the macro we have given a fixed range of C18 TO C4018...what I need is that it starts from C18 but goes till where the cells are filled with numeric values....like if the numeric values are till C50....it runs this macro on C18 TO C50......I think we need to change the range setting.....thank u guys ...u guys are great

It doesnot matter what range it is c18: to what ever, the code works

anilsolipuram is right about his solution (which is much better than a slow For Each ... Next statement). However, if you ever need to select a range from a certain spot to the last used cell in a column then it would look like the following code.

Range(Range("C18"), Cells(Rows.Count, Range("C18").Column).End(xlUp))

Cheers!

THANKS TO BOTH OF U...Anill is very right but it shows the selected range and highlights it..and Richardreye if I follow this range format , in the end of the macro it shows VB error
my code is
Private Sub CommandButton1_Click()

Set rng = Range(Range("C18"), Cells(Rows.Count, Range("C18").Column).End(xlUp))

For Each cl In rng

If (Int(Right(cl, 1)) = 7 And Not (Left(Range("J12"), 3) = "AAA" Or Left(Range("J12"), 3) = "BBB" Or Left(Range("J12"), 2) = "CC" Or Left(Range("J12"), 2) = "DD")) Then

cl.Select
MsgBox cl.Address

End If

Next

End Sub

THANKS AGAIN TO BOTH OF U

I'm not sure about what error you are getting because I pasted your code into a spreadsheet and it worked fine. I would simply suggest making this small change to anil's code

Sub Button1_Click()

Set rng = Range("C18:C4018") 'range to searched

On Error Resume Next
'find cells ending with 7
rng.Find(What:="*7", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If Err.Description <> "" Then
MsgBox "no cells ending with 7"
Err.Clear
Else
On Error Resume Next
If Left(Range("J12"), 3) = "AAA" Or Left(Range("J12"), 3) = "BBB" Or Left(Range("J12"), 2) = "CC" Or Left(Range("J12"), 2) = "DD" Then
If Err.Description <> "" Then
Err.Clear
MsgBox "j12 not in correct format"
GoTo a:
Else

End If

Else
MsgBox "j12 not in correct format"
End If
End If
a:
End Sub

Cheers!

Thank u Richardreye.....I still do not understand why I am getting that error. If u see that code which was written by Mangesh is a small one...I am getting run time error 13 ...after I run the macro...It runs fine but I dont know why it gives me an error in the end....do u have any idea, how can I remove that error...thanks....

On which line is the error comming from and what is the error message

HI ....after it checks all the cells which are filled...and in the end it gives me an error...run time error 13, type mismatch...and it highlights this row
If (Int(Right(cl, 1)) = 7 And Not (Left(Range("J12"), 3) = "AAA" Or Left(Range("J12"), 3) = "BBB" Or Left(Range("J12"), 2) = "CC" Or Left(Range("J12"), 2) = "DD")) Then


HERE IS MY FULL CODE:
Private Sub CommandButton1_Click()

Set rng = Range(Range("C18"), Cells(Rows.Count, Range("C18").Column).End(xlUp))

For Each cl In rng

If (Int(Right(cl, 1)) = 7 And Not (Left(Range("J12"), 3) = "AAA" Or Left(Range("J12"), 3) = "BBB" Or Left(Range("J12"), 2) = "DD" Or Left(Range("J12"), 2) = "CC")) Then

cl.Select
MsgBox cl.Address

End If

Next

End Sub

Try this


Public Sub CommandButton1_Click()

Set rng = Range(Range("C18"), Cells(Rows.Count, Range("C18").Column).End(xlUp))

For Each cl In rng
On Error Resume Next
If (Int(Right(cl, 1)) = 7 And Not (Left(Range("J12"), 3) = "AAA" Or Left(Range("J12"), 3) = "BBB" Or Left(Range("J12"), 2) = "DD" Or Left(Range("J12"), 2) = "CC")) Then
If Err.Description <> "" Then
Err.Clear
GoTo a:
Else
cl.Select
MsgBox cl.Address
End If

End If
a:
Next

End Sub

thanks frnd...thank u so much

Hi Vnagpal,

sorry, I was not there yesterday.

As for your query on the error, the solution provided by anilsolipuram will work, but it would also avoid other errors. If you want to try to resolve the error, put the following msgbox after you set your range.

Set rng = Range(Range("C18"), Cells(Rows.Count, Range("C18").Column).End(xlUp))
MsgBox = rng.Address

The msgbox displas the address of the entire range on which your operation is performed. Check whether the range is correct and what you indeed wanted to use.

Also in the last cell of the range, what was the value when you were getting the erro.

- Mangesh