MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VBA to search a column for text matching a named range cell.


Posted by Daniel Cremin on December 12, 2001 11:11 AM

Hi Thanks for helping me whoever reads this. I have an underlying sheet called Processing with a load of student details, test marks and percentages and grades on them. i have a sheet called a Student Details Editor where a load of cells dislplay certain data (Student Name, Teacher, Test marks for the 5 tests) via INDEX functions that use the index no of a student name picked using a list box of student names from the processing page (C8:C38).I need to be able to search on the processing sheet and select the cell in the range C8:C38 that matches the named range (cell) SDEStudentName on the Student Details Editor Sheet. I probably need a Do Until statement but really am stuck and need a way to offset to the cell that matches the SDEStudentName cell on the other cell (same student name) so that if i update details on a specialised cell bwlow on the Student Details Editor i can offset and paste (i can do this bit of the VBA procdure myself).

Thanks again


Posted by Jacob on December 12, 2001 11:27 AM

Hi
Maybe this will help
lets say that you have the cell you want to search for selected when you activate the macro

searchvalue=activecell.value

Sub test()

For x = 8 To 38
Range("c" & x).Select
If ActiveCell.Value = searchvalue Then
'your code here
Else
End If

Next x
End Sub

hope this helps you get started

Posted by Daniel Cremin on December 12, 2001 11:31 AM

Ok thanks + can i change the activecell.value to range (

Above is the message.

Posted by jacob on December 12, 2001 11:37 AM

Re: Ok thanks + can i change the activecell.value to range (

Sure you can put searchvalue=range("A1").value

you can put any cell range in place of A1

Jacob


Posted by Daniel Cremin on December 12, 2001 11:43 AM

One more question

For your code you put

For x = 8 To 38
Range("c" & x).Select

wy not range (C8:C38) im not criticising im just curious as to how it will work (im opening my system now).


Posted by Juan Pablo G. on December 12, 2001 11:47 AM

Some suggestions...

Altough this macro works, we're here to push things further, right ?

So, that said, loops are a terrible thing ! they're slow... Why not use the Find method for example ?

Set C = Range("C8:C38).Find What:=Searchvalue
If Not C is Nothing 'Found it there

else 'Didn't find it

End If

Another option, using the same loop, but simpler...

For each Cll in Range("C8:C38")
If Cll = SearchValue then 'Found It
Exit For
End If
Next Cll

Juan Pablo G.


Posted by Jacob on December 12, 2001 11:49 AM

Re: One more question

The code needs to check each line so you could so range(C8).select
then code
range(c9)
...
...
...
range(c38)

but you would have to type in all the code and it would be a waste of time by making the row number a variable (x) the computer can do it automatically.

Jacob


Posted by Daniel on December 12, 2001 11:57 AM

Next bit !

Hi thanks for the help so far.Ive got it now to select the correct cell. But i need to know where in your code i could put the next bit because i dont understand about the Else argument being left empty. Ok i want to be able to make the student name (the cell in the C column that i select equal to whatever i have typed in a cell on the Student Details Editor (e.g. A11) and then offset by 1 to the right. Can you see Ne probs with putting this code straight after what you have written - At the end of the macro it keeps selecting cell C38 which worries me in case it thinks that is the activecell somehow (nothing in this cell its just cos its at the end of the range).

Posted by Jacob on December 12, 2001 12:07 PM

Re: Next bit !

hi

Sub test()

For x = 8 To 38
Range("c" & x).Select
If ActiveCell.Value = searchvalue Then
'your code here
Else
End If

Next x
End Sub

Whatever code you want to run whenever it finds the search key put that in the spot ('your code here) When it finds the search key it will run your code if it doesnt find it it will go to the next row until it gets to 38

Jacob

Posted by Daniel on December 12, 2001 12:12 PM

Can you explain how it works

Its working now and my code line works perfectly. What is this searchvalue bit is it some sort of VBA function, im sure its not a Dim Variable but you declared it before the macro started and what is the x bit (ive heard of something in VBA involving For and Next statements instead of Do Until and Loops but i dont have ne info - i need to be able to justify how i have been able to use this VBA you see.

Posted by Jacob on December 12, 2001 1:01 PM

Re: Can you explain how it works

Searchvalue is just the name I chose for the value. You should Dim it at the beginning, but you dont have to. You can change the name to whatever you want. The X is a variable that will increase by 1 each loop so For X = 8 To 38 says that X = 8 for the first loop, then 9, then 10, then 11 etc and when it gets to 38 it stops looping. You could change those numbers to whatever you want i.e. for x = 1 to 100 would go from 1 to 100 by 1 at a time (1,2,3,4,...,99,100).

Hope this helps

Jacob