vba lookup

[avishain]

hello,

im using excel 2007 and need assitstance with the following:

i got data from colomn A to F.

in colomn A i got unique codes.

in colomn C and D i got numbers.(some cells are empty)

i want to assian a macro that when i click it it will ask me :
"plz eneter a code"- once i did it it will search in colomn A the code and will color cell C of the same row if it consist of a number.

if there is no number in colomn C,it will search colomn D,and if there is a number -will color it.

if there is no number in both C&D -it will pop up a msg "there is no match".




thank u very much

 

[wigi]

In columns C and D, are these numbers hardcoded or the result of a formula?
In column A, what are typical codes?

 

[Domenic]

Maybe...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] Ans [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] FoundCell [color=darkblue]As[/color] Range

    Ans = InputBox("Please enter a code for which to search...", "Search")
    
    [color=darkblue]If[/color] Ans = "" [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    
    [color=darkblue]Set[/color] FoundCell = Columns("A").Find(what:=Ans, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    
    [color=darkblue]If[/color] [color=darkblue]Not[/color] FoundCell [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
    
        [color=darkblue]If[/color] IsNumeric(Range("C" & FoundCell.Row).Text) [color=darkblue]Then[/color]
            Range("C" & FoundCell.Row).Interior.Color = RGB(255, 255, 0)
        [color=darkblue]ElseIf[/color] IsNumeric(Range("D" & FoundCell.Row).Text) [color=darkblue]Then[/color]
            Range("D" & FoundCell.Row).Interior.Color = RGB(255, 255, 0)
        [color=darkblue]Else[/color]
            MsgBox "There is no match...", vbExclamation
        [color=darkblue]End[/color] [color=darkblue]If[/color]
        
    [color=darkblue]Else[/color]
    
        MsgBox "There is such code...", vbExclamation
        
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[chuckles1066]

My feeble attempt:

Sub chuckles1066()

Dim lastrow As Long
Dim Message, MyValue
Dim a As Long
Dim coloured As Boolean

Message = "Enter a code"
lastrow = Range("A" & Rows.Count).End(xlUp).Row
MyValue = Application.InputBox(Message, Type:=3)
coloured = False
For a = 2 To lastrow
Select Case (Cells(a, 1).Value)
    Case MyValue
        If Cells(a, 3).Value <> "" Then
        Cells(a, 3).Interior.ColorIndex = 4
        coloured = True
        End If
        If Cells(a, 4).Value <> "" And coloured = False Then
        Cells(a, 4).Interior.ColorIndex = 4
        coloured = True
        End If
        If coloured = False Then MsgBox "No match"
End Select
Next a
End Sub

Others will be able to provide more elegant and efficient solutions

(I'm learning so any pointers as to any stupid things I'm doing are greatly appreciated)

 

[avishain]

Maybe...

[FONT=Verdana][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=darkblue]Sub[/COLOR] test()[/FONT]
 
[FONT=Verdana]   [COLOR=darkblue]Dim[/COLOR] Ans [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR][/FONT]
[FONT=Verdana]   [COLOR=darkblue]Dim[/COLOR] FoundCell [COLOR=darkblue]As[/COLOR] Range[/FONT]
 
[FONT=Verdana]   Ans = InputBox("Please enter a code for which to search...", "Search")[/FONT]
 
[FONT=Verdana]   [COLOR=darkblue]If[/COLOR] Ans = "" [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]
 
[FONT=Verdana]   [COLOR=darkblue]Set[/COLOR] FoundCell = Columns("A").Find(what:=Ans, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)[/FONT]
 
[FONT=Verdana]   [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] FoundCell [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR][/FONT]
 
[FONT=Verdana]       [COLOR=darkblue]If[/COLOR] IsNumeric(Range("C" & FoundCell.Row).Text) [COLOR=darkblue]Then[/COLOR][/FONT]
[FONT=Verdana]           Range("C" & FoundCell.Row).Interior.Color = RGB(255, 255, 0)[/FONT]
[FONT=Verdana]       [COLOR=darkblue]ElseIf[/COLOR] IsNumeric(Range("D" & FoundCell.Row).Text) [COLOR=darkblue]Then[/COLOR][/FONT]
[FONT=Verdana]           Range("D" & FoundCell.Row).Interior.Color = RGB(255, 255, 0)[/FONT]
[FONT=Verdana]       [COLOR=darkblue]Else[/COLOR][/FONT]
[FONT=Verdana]           MsgBox "There is no match...", vbExclamation[/FONT]
[FONT=Verdana]       [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR][/FONT]
 
[FONT=Verdana]   [COLOR=darkblue]Else[/COLOR][/FONT]
 
[FONT=Verdana]       MsgBox "There is such code...", vbExclamation[/FONT]
 
[FONT=Verdana]   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR][/FONT]
 
 
[FONT=Verdana][COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]

works great,thank u.

anothe ques.

if i wanted that the colored cell will be the active cell right after excel color it,what would i need to add to the code?

 

[wigi]

My contribution:

Sub coloringnumbers()
    On Error Resume Next
    With Columns(1).Find(Application.InputBox("plz enter a code", "code", , , , , , 1), , xlValues, xlWhole).Offset(, 2).Resize(, 2).SpecialCells(2, 1).Cells(1)
        .Interior.ColorIndex = 5
        .Select
    End With
    If Err.Number Then
        Err.Clear
        MsgBox "no number found or another error occurred.", vbCritical, "error"
    End If
End Sub

This will work if codes are numbers, and the numbers in C & D are hardcoded. I took these decisions since you did not reply to my earlier question.

 

[avishain]

My contribution:

Sub coloringnumbers()
    On Error Resume Next
    With Columns(1).Find(Application.InputBox("plz enter a code", "code", , , , , , 1), , xlValues, xlWhole).Offset(, 2).Resize(, 2).SpecialCells(2, 1).Cells(1)
        .Interior.ColorIndex = 5
        .Select
    End With
    If Err.Number Then
        Err.Clear
        MsgBox "no number found or another error occurred.", vbCritical, "error"
    End If
End Sub

This will work if codes are numbers, and the numbers in C & D are hardcoded. I took these decisions since you did not reply to my earlier question.

thank u very much for your help

 

[Domenic]

works great,thank u.

You're welcome!

if i wanted that the colored cell will be the active cell right after excel color it,what would i need to add to the code?

Try replacing...

[font=Verdana][color=darkblue]If[/color] IsNumeric(Range("C" & FoundCell.Row).Text) [color=darkblue]Then[/color]
    Range("C" & FoundCell.Row).Interior.Color = RGB(255, 255, 0)
[color=darkblue]ElseIf[/color] IsNumeric(Range("D" & FoundCell.Row).Text) [color=darkblue]Then[/color]
    Range("D" & FoundCell.Row).Interior.Color = RGB(255, 255, 0)
[color=darkblue]Else[/color]
    MsgBox "There is no match...", vbExclamation
[color=darkblue]End[/color] [color=darkblue]If[/color]
        [/font]

with

[font=Verdana][color=darkblue]If[/color] IsNumeric(Range("C" & FoundCell.Row).Text) [color=darkblue]Then[/color]
    [color=darkblue]With[/color] Range("C" & FoundCell.Row)
        .Interior.Color = RGB(255, 255, 0)
        .Select
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]ElseIf[/color] IsNumeric(Range("D" & FoundCell.Row).Text) [color=darkblue]Then[/color]
    [color=darkblue]With[/color] Range("D" & FoundCell.Row)
        .Interior.Color = RGB(255, 255, 0)
        .Select
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]Else[/color]
    MsgBox "There is no match...", vbExclamation
[color=darkblue]End[/color] [color=darkblue]If[/color]
        [/font]

 

[avishain]

You're welcome!



Try replacing...

[FONT=Verdana][COLOR=darkblue]If[/COLOR] IsNumeric(Range("C" & FoundCell.Row).Text) [COLOR=darkblue]Then[/COLOR][/FONT]
[FONT=Verdana]   Range("C" & FoundCell.Row).Interior.Color = RGB(255, 255, 0)[/FONT]
[FONT=Verdana][COLOR=darkblue]ElseIf[/COLOR] IsNumeric(Range("D" & FoundCell.Row).Text) [COLOR=darkblue]Then[/COLOR][/FONT]
[FONT=Verdana]   Range("D" & FoundCell.Row).Interior.Color = RGB(255, 255, 0)[/FONT]
[FONT=Verdana][COLOR=darkblue]Else[/COLOR][/FONT]
[FONT=Verdana]   MsgBox "There is no match...", vbExclamation[/FONT]
[FONT=Verdana][COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR][/FONT]

with

[FONT=Verdana][COLOR=darkblue]If[/COLOR] IsNumeric(Range("C" & FoundCell.Row).Text) [COLOR=darkblue]Then[/COLOR][/FONT]
[FONT=Verdana]   [COLOR=darkblue]With[/COLOR] Range("C" & FoundCell.Row)[/FONT]
[FONT=Verdana]       .Interior.Color = RGB(255, 255, 0)[/FONT]
[FONT=Verdana]       .Select[/FONT]
[FONT=Verdana]   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR][/FONT]
[FONT=Verdana][COLOR=darkblue]ElseIf[/COLOR] IsNumeric(Range("D" & FoundCell.Row).Text) [COLOR=darkblue]Then[/COLOR][/FONT]
[FONT=Verdana]   [COLOR=darkblue]With[/COLOR] Range("D" & FoundCell.Row)[/FONT]
[FONT=Verdana]       .Interior.Color = RGB(255, 255, 0)[/FONT]
[FONT=Verdana]       .Select[/FONT]
[FONT=Verdana]   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR][/FONT]
[FONT=Verdana][COLOR=darkblue]Else[/COLOR][/FONT]
[FONT=Verdana]   MsgBox "There is no match...", vbExclamation[/FONT]
[FONT=Verdana][COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR][/FONT]

thank u again, i really appricate your and the others help....

 

[Domenic]

You're very welcome! And thanks for the feedback!