VBA code to search by meeting a criteria

awale

New Member
Joined
Sep 2, 2015
Messages
5
(completely new to excel VBA )

Hello

I am having problems with a VBA Excel 2010 program code.

I am trying to read data from a spreadsheet on excel 2010. what I have is a set of data (see below) and I am trying to write a code that will let me use a msg box and ask me to write down the name I am looking for e.g. "Name 1" from the list of names in the column and then I want to set a criteria where if the number in the columns are equal to zero and again for a different column = 0 ("name 5"), then highlight red any number in column "Name 8 and Name 9" that is greater than let say 30 (just a random example). the important thing is, the red highlight of column "Name 8/9" must only occur if the numbers is row "Name 1" and "Name 5" are equal to zero.

I have already done this but I only used the column numbers e.g. A1:A5. now I need to use the name of the column because I want to use the code for a different excel spreadsheets but the names of columns are in different positions for each excel sheet, but if I use the names, no matter which column along excel they are I will always find the right column I am looking for and set the criteria.

the criteria for "Name 1/5" will always be = 0 or =1 but the program has to ask me to choose that when I search for it.

if you look below at the example, you can see the red highlight are when criteria of =0 is met for Name 1 and Name 5 and the number in Name8/9 are greater than 30. when it is not greater than 30 and it still meets the criteria it is highlighted blue in the excel spreadsheet example. ALL OTHER NAMES MUST BE IGNORED.

SEE EXAMPLE BELOW hopefully this will make sense. “hopefully"


Name 1Name 2Name 3Name 4Name 5Name 6Name 7Name 8Name 9Name 10
00100158351419
00000141456874
10101023189887
00100165365289
00001124954775
11101058875914
01000074418432
11001096257496
00000087351553
00100157494847
10101163842365
01000021546912
00100054235454
11001188347788
00100078486869
10100196871465
10001021965425
01000054727829
01100162382278
00000021496554
10101117659899
00000059155670
01100036122954
10001029495554

THANKS ALOT

<tbody>
</tbody>

 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Arjan77

Board Regular
Joined
Feb 9, 2015
Messages
178
Just to get you started, if you have a userform with a textbox and a commandbutton use this code:

Code:
Private Sub CommandButton1_Click()
Dim Ws1 As Worksheet

Set Ws1 = Worksheets(1)

For i = 1 To Ws1.Cells(Columns.Count, 5).End(xlToRight).Column
If TextBox1.Value = Ws1.Cells(1, i) Then
MsgBox i
GoTo Finish:
End If
Next

Finish:
End Sub

This will get you the number of the column with the name you typed in the textbox.

From there you will have to extend your code to perform the formatting of the cells, if you never used VBA this requires a lot of time.
 

awale

New Member
Joined
Sep 2, 2015
Messages
5
THIS IS WHAT I HAVE SO FAR

Code:
Private Sub CommandButton21_Click()

searchstring = InputBox("Input name?")

Set coll = Rows(1).Find(What:=searchstring, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If coll Is Nothing Then
    MsgBox "Name not found"
    Exit Sub
Else
    coll = coll.Column
    Lrow = Range(Cells(2, coll), Cells(2, coll)).CurrentRegion.Rows.Count
End If

    Cells.Interior.ColorIndex = 0
    For Each rw In Range(Cells(2, coll), Cells(Lrow, coll))

        If Application.Sum(rw.Resize(, 4)) = 0 Then
            rw.Interior.ColorIndex = 3
            For Each cll In rw.offset(, 4).Resize(, 18).Cells
                If cll.Value > 50 Then cll.Interior.ColorIndex = 3
            Next cll
        End If
    Next rw
End Sub

I want to be able to search for three three diffeent name and set the criteria( a message box that asks me whether I want it to be 1 or 0 ). so search name 1 ask me for criteria , 1 or 0 then name 2 then name 3.
 

Arjan77

Board Regular
Joined
Feb 9, 2015
Messages
178
Why don't you create a userform, you can enter all criteria at the same time. I would use a combobox so you can just pick the appropriate name.

Add a combobox to the userform and use this code:

Code:
Private Sub UserForm_Initialize()

For i = 1 To ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
ComboBox1.AddItem ActiveSheet.Cells(1, i).Value
Next

ComboBox1.ListIndex = 0

End Sub

The combobox will list all your column names.
Your column number will equal combobox1.listindex +1

Add two more comboboxes initialize them the same way, after that you use a commandbutton to perform actions for all three comboboxes.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,484
Messages
5,770,364
Members
425,612
Latest member
martinijr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top