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

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

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

Above is the message.

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

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).

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.

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

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).

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

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.

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