Find text in Row and use as criteria for search in column where text is found

[sindrefm]

Hi

I am hoping for help here.

Case:
I have a sheet with several headers on first row.
On the headers columns I have different values.
I am trying to find a way to find the text for the headers, and then
search for values on that column.
Can anyone help? :)

[Khanonline]

Hey,

Do you want something if matched in First Row then shows the Values List among that Particular Column?

Best
Khanonline

[sindrefm]

Hi Khanonline,

What I am going to do is comparing/validate values in rows from two header columns. But I need to find those header columns on first row with some code. I have tried some, but cannot find a good code.

Ex
Headers: name, type, signal
Find "name" and store the column location
Find "type" and store the column location
Compare/validate values from the two header columns

Thanks

[sindrefm]

12503

Here is just an example picture.

[mancubus]

welcome to vbax sindrefm.

be more specific.

what you're trying to do is unclear.

you can upload a sample file here that shows the current table structure and the desired output.

[sindrefm]

12504

Thank you mancubus.

Yes, I know, it was a bit unclear.

What I want:

I want a vba code attach to different buttons in Sheet2.
When pressing one button I want it to compare row values under the header "Name" and "Type" on Sheet1.
It is essential that it finds the the header with looking anywhere on Row 1, because the header "Name" can for example be on A1, B1 and so on. Same with the other headers.
The rules for values under "Name" and "Type" is that BMW can only have type A. If BMW have type B, I want the code to mark the cell with red.
The same with values under "Name" and "Signal", FORD can only have signal Y.
See picture and attachment.

12505

[mancubus]

so we only know if col A is BMW and col B is not A color that cell in B.

adopt below for other conditions:



Sub ColorColB()


Dim i As Long

With ActiveSheet
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
If .Cells(i, 1).Value = "BMW" Then
If .Cells(i, 2).Value <> "A" Then .Cells(i, 2).Interior.Color = vbRed
ElseIf .Cells(i, 1).Value = "FORD" Then
If .Cells(i, 2).Value <> "B" Then .Cells(i, 2).Interior.Color = vbRed
ElseIf .Cells(i, 1).Value = "LAMBORGHINI" Then
If .Cells(i, 2).Value <> "C" Then .Cells(i, 2).Interior.Color = vbRed
'...
'...
'...
'...
End If
Next
End With


End Sub


Sub ColorColC()


Dim i As Long

With ActiveSheet
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
If .Cells(i, 1).Value = "BMW" Then
If .Cells(i, 3).Value <> "X" Then .Cells(i, 2).Interior.Color = vbRed
ElseIf .Cells(i, 1).Value = "FORD" Then
If .Cells(i, 3).Value <> "Y" Then .Cells(i, 2).Interior.Color = vbRed
ElseIf .Cells(i, 1).Value = "LAMBORGHINI" Then
If .Cells(i, 3).Value <> "Z" Then .Cells(i, 2).Interior.Color = vbRed
'...
'...
'...
'...
End If
Next
End With


End Sub

[mancubus]

btw, i would use conditional formatting if i have a 3 columns validation list.

BMW A X
FORD B Y
...
...

see the attached for an added worksheet which holds the validation list.
defined range: formulas / name manager
cond format: home / conditional formatting / manage rules

[sindrefm]

Yes, that was what I am looking for. Thank you.

For the following code, do you know a way to like find the column with the header "Name", "Type" and "Signal". For example the "Name" column is not always on the column "A" (1).
I want to find the headers "Name" and "Type", and then run the validation code Sub ColorCol().

With ActiveSheet
For i = 2 To .Cells(.Rows.Count, x).End(xlUp).Row
If .Cells(i, x).Value = "BMW" Then

I want to find the red x here.

And attach the run of code "Sub ColorCol()" to a button on Sheet3.

Thanks for the help on this.

[mancubus]

you are welcome.

copy below procedures in a standard module.



Sub ColorType()
Dim strName As String, strType As String
Dim i As Long, ColName As Long, ColType As Long

strName = "Name"
strType = "Type"

With ActiveSheet
ColName = .Rows(1).Find(strName).Column
ColType = .Rows(1).Find(strType).Column
For i = 2 To .Cells(.Rows.Count, ColName).End(xlUp).Row
If .Cells(i, ColName).Value = "BMW" Then
If .Cells(i, ColType).Value <> "A" Then .Cells(i, ColType).Interior.Color = vbRed
ElseIf .Cells(i, ColName).Value = "FORD" Then
If .Cells(i, ColType).Value <> "B" Then .Cells(i, ColType).Interior.Color = vbRed
ElseIf .Cells(i, ColName).Value = "LAMBORGHINI" Then
If .Cells(i, ColType).Value <> "C" Then .Cells(i, ColType).Interior.Color = vbRed
'...
'...
'...
'...
End If
Next
End With

End Sub
Sub ColorSignal()
Dim strName As String, strSignal As String
Dim i As Long, ColName As Long, ColSignal As Long

strName = "Name"
strSignal = "Signal"

With ActiveSheet
ColName = .Rows(1).Find(strName).Column
ColType = .Rows(1).Find(strSignal).Column
For i = 2 To .Cells(.Rows.Count, ColName).End(xlUp).Row
If .Cells(i, ColName).Value = "BMW" Then
If .Cells(i, ColSignal).Value <> "A" Then .Cells(i, ColSignal).Interior.Color = vbRed
ElseIf .Cells(i, ColName).Value = "FORD" Then
If .Cells(i, ColSignal).Value <> "B" Then .Cells(i, ColSignal).Interior.Color = vbRed
ElseIf .Cells(i, ColName).Value = "LAMBORGHINI" Then
If .Cells(i, ColSignal).Value <> "C" Then .Cells(i, ColSignal).Interior.Color = vbRed
'...
'...
'...
'...
End If
Next
End With
End Sub



you dont know how to assign a macro to a CommandButton?

i recommend you read and learn the basics of VBA, coding, forms and controls before using macros and userforms

until then do it like so:

click Insert Mode in Controls Group in Developer tab.
click CommandButton from activeX controls.
+ sign appears.
position the cursor in worksheet.
press and hold left button of mouse
move right and then down while holding the left button to resize the CommandButton.
release the left button.
now CommandButton is inserted.

if you like to change the its caption, right click CommandButton, select Edit from CommandButton Object, clear CommandButton1 and type "Color Type" (or whatever text you like for CommandButton's caption)
click any cell in worksheet.

if you like to change its name, right click CommandButton again, select Properties. Clear CommandButton1 and type, for example "CmdBtnColType", enter.
close properties window.

double click command button
code module of the worksheet opens with automatically inserted below lines
Private Sub CommandButton1_Click()
End Sub
or if the CommandButton's name has changed:
Private Sub CmdBtnColType_Click()
End Sub

insert this line before End Sub: Call ColorType

save the file.


repeat above steps for Signal, replacing Type's with Signal's.

[sindrefm]

That is awesome. It is exactly what I was looking for. Thank you very much.
Now I have something to build on.

The "Modules" in VBA, can it be compared with almost like a function block?
Can I call a Module from another Module?
In Module1 I can for example:
Sub CallModule2()
Call Module2
End Sub
And then it runs the code on Module2?

It seems that I cannot change the name of the module in this view:
12512

Is it a way to change the Module name here?

Thank you!

[mancubus]

welcome.


modules are for storing functions and subprocedures or macros.
the collection of modules (say, in a workbook) is called a VBAProject.

you can call any function or subprocedure which is stored in any module. you can also run a macro stored in a different workbook.

you can change the names of modules from properties (VBe / view / properties window).

these are far easy questions to find answers.
don't be shy; ask google. :devil2:

[sindrefm]

Great. Thank you very much :)

[sindrefm]

Hi again Mancubus,

I have one question here again.

What I want:
I got a column named "Number". If it is a mismatch in a cell (red cell), I want to write the cooresponding number in the Number column in another excel sheet for each error (red cell).
Example from the pictures:


12533

12534

Can you help me on the way with this?

Thank you.

[mancubus]

hi.
add a blank sheet and rename it as "errors".

i assume we don't know the column of "Number" as well.



Sub ColorType()
Dim strName As String, strType As String, strNum As String
Dim i As Long, ColName As Long, ColType As Long, ColNum As Long

strName = "Name"
strType = "Type"
strNum = "Number"

With ActiveSheet
ColName = .Rows(1).Find(strName).Column
ColType = .Rows(1).Find(strType).Column
ColNum = .Rows(1).Find(strNum).Column
For i = 2 To .Cells(.Rows.Count, ColName).End(xlUp).Row
If .Cells(i, ColName).Value = "BMW" Then
If .Cells(i, ColType).Value <> "A" Then
.Cells(i, ColType).Interior.Color = vbRed
Worksheets("errors").Range("A" & Rows.Count).End(xlUp).Offset(1) = .Cells(i, ColNum)
End If
ElseIf .Cells(i, ColName).Value = "FORD" Then
If .Cells(i, ColType).Value <> "B" Then
.Cells(i, ColType).Interior.Color = vbRed
Worksheets("errors").Range("A" & Rows.Count).End(xlUp).Offset(1) = .Cells(i, ColNum)
End If
ElseIf .Cells(i, ColName).Value = "LAMBORGHINI" Then
If .Cells(i, ColType).Value <> "C" Then
.Cells(i, ColType).Interior.Color = vbRed
Worksheets("errors").Range("A" & Rows.Count).End(xlUp).Offset(1) = .Cells(i, ColNum)
End If
'...
'...
'...
'...
End If
Next
End With
End Sub
Sub ColorSignal()
Dim strName As String, strSignal As String, strNum As String
Dim i As Long, ColName As Long, ColSignal As Long, ColNum As Long

strName = "Name"
strSignal = "Signal"
strNum = "Number"

With ActiveSheet
ColName = .Rows(1).Find(strName).Column
ColSignal = .Rows(1).Find(strSignal).Column
ColNum = .Rows(1).Find(strNum).Column
For i = 2 To .Cells(.Rows.Count, ColName).End(xlUp).Row
If .Cells(i, ColName).Value = "BMW" Then
If .Cells(i, ColSignal).Value <> "X" Then
.Cells(i, ColSignal).Interior.Color = vbRed
Worksheets("errors").Range("A" & Rows.Count).End(xlUp).Offset(1) = .Cells(i, ColNum)
End If
ElseIf .Cells(i, ColName).Value = "FORD" Then
If .Cells(i, ColSignal).Value <> "Y" Then
.Cells(i, ColSignal).Interior.Color = vbRed
Worksheets("errors").Range("A" & Rows.Count).End(xlUp).Offset(1) = .Cells(i, ColNum)
End If
ElseIf .Cells(i, ColName).Value = "LAMBORGHINI" Then
If .Cells(i, ColSignal).Value <> "Z" Then
.Cells(i, ColSignal).Interior.Color = vbRed
Worksheets("errors").Range("A" & Rows.Count).End(xlUp).Offset(1) = .Cells(i, ColNum)
End If
'...
'...
'...
'...
End If
Next
End With
End Sub

[sindrefm]

thanks, works very well.

One thing I have a problem with is the Find function.
ex.

strNum = "Number"
ColNum = .Rows(1).Find(strNum).Column
Worksheets("errors").Range("A" & Rows.Count).End(xlUp).Offset(1) = .Cells(i, ColNum)

Sometimes when I'm running this code, it seems that it wont find the "number" column and write to the Errors sheet. But if I rename strNum="Number1" and the column in the active sheet to "Number1" it works again.
Do you have any idea why or how this i happening?

[mancubus]

welcome.

assuming row 1 contains all the values to find, i did not add error handler to the code and used only required parameter.

if find method does not find a match it throws RTE 91 error.

do you get any error message?

visit here for details about Range.Find Method.
http://msdn.microsoft.com/en-us/library/office/ff839746(v=office.15).aspx


The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you do not specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

[sindrefm]

No I do not get any errors. It is just that it does not print the number in the Errors sheet, but the cell gets red color (.Cells(i, ColSignal).Interior.Color = vbRed). So therefore I assume it does no find the column.
But when I change from:

Header column "Number"

strNum = "Number"
ColNum = .Rows(1).Find(strNum).Column
Worksheets("errors").Range("A" & Rows.Count).End(xlUp).Offset(1) = .Cells(i, ColNum)

To:
Header column "Number1"

strNum = "Number1"
ColNum = .Rows(1).Find(strNum).Column
Worksheets("errors").Range("A" & Rows.Count).End(xlUp).Offset(1) = .Cells(i, ColNum)

it finds the number and print on error sheet.

I find that very strange.

[mancubus]

if it changes the fill color it should also execute consecutive line of the code.

check iy you adopted the code for all scenarios. did you cope all lines?



i need to see the file (replace sensitive info with any value you imagine) and the code you use.

[sindrefm]

I copy all the lines yes.

Here is an example:

Sub Check()
strNumber = "Number"
strType = "Type"
strLow = "Low"
strHigh = "High"
strModel = "Model"


With Worksheets("Sheet")
ColNumber = .Rows(1).Find(strNumber).Column
ColType = .Rows(1).Find(strType).Column
ColLow = .Rows(1).Find(strLow).Column
ColHigh = .Rows(1).Find(strHigh).Column
ColModel = .Rows(1).Find(strModel).Column

For i = 2 To .Cells(.Rows.Count, ColType).End(xlUp).Row

If .Cells(i, ColType).Value = "BMW" Then
If IsEmpty(.Cells(i, ColLow).Value) Then
.Cells(i, ColLow).Interior.Color = vbRed
Worksheets("errors").Range("B" & Rows.Count).End(xlUp).Offset(1) = .Cells(i, ColNumber)
End If
If IsEmpty(.Cells(i, ColHigh).Value) Then
.Cells(i, ColHigh).Interior.Color = vbRed
Worksheets("errors").Range("B" & Rows.Count).End(xlUp).Offset(1) = .Cells(i, ColNumber)
End If
If IsEmpty(.Cells(i, ColModel).Value) Then
.Cells(i, ColModel).Interior.Color = vbRed
Worksheets("errors").Range("B" & Rows.Count).End(xlUp).Offset(1) = .Cells(i, ColNumber)
End If
End If
Next
End With
End Sub

[mancubus]

pls use code tags when posting your code here.
clicking the # button will do it for you.

the condition in the previous message is different than the condition in the first message. pls care to provide examples that reflect this.

btw, when a condition is met, all lines between If-EndIf block are executed on condition that they are executable.

so if the code changes the fill color, it will also write the Number info to sheet "error".

if the cell that is supposed to contain the number is blank then nothing will change in sheet error.


unless i can see the non working file this thread is closed for me.

[sindrefm]

I found out why it failed.
On the row(1) I have several columns with some matching words in it.
I give you an ex.
I have column headers naming Type Type1 Type2
If I am to find strType = "Type" on the row(1) , it happens that it sometimes find the header on row(1) containing the string "Type", like "Type1".
And when "Type1" cells containing no values, it will then write no values in "errors". Thats why I didnt get anything on the "errors" sheet.

Do you know a way to do like an complete "match" search/find? So that it dont search on strings containing the values, but search after the complete value.

Thanks.

[sindrefm]

Seems like find(What:="Type", LookAt:=xlWhole) fixed it :)

[sindrefm]

I am trying to get the header on the error cell into "error" sheet column C. Any idea what I'm doing wrong?

Working sheet
12545

Error sheet
12546

#Worksheets("errors").Range("B" & Rows.Count).End(xlUp).Offset(1) = .Cells(i, ColType)
#Worksheets("errors").Range("C" & Rows.Count).End(xlUp).Offset(1) = .Cells(i, (ColModel - i))