trouble with VBA code, how do i activate a cell?

[bigdan]

I'm trying to find a value in a list. I've written some code that is able to find the value just find, ie a msgbox will say "found the value at E8", but I'd like to be able to bring the active cell to where the value is found. ie I'd like Excel to bring the cursor to E8. can anyone advise?

Here's the code. I've commented out the one line where I tried to activate the relevant cell. That command gave a compile error.

Btw I haven't posted much code here before, what is the etiquette when doing so? I'm guessing it's not just to paste the code in here?


For Each cell In Range("E1:E30")
If cell.Value = search Then
'cell.Row.Activate (this command gave the error)
MsgBox "found the value at " & "Cell E" & (i + 1)
Exit For
End If
i = i + 1
Next cell

 

[6StringJazzer]

The .Row part is what's messing you up.

cell.Activate

or also

cell.Select

Pasting in the code is good. What's even better is to put code tags around your code to preserve formatting and show color-coding. After you paste your code, select it, then click the VBA button on the edit controls to add the code tags.

You also don't need to keep track with i when you already know which cell it is.

For Each cell In Range("E1:E30")
   If cell.Value = search Then
      cell.Activate
      MsgBox "found the value at " & cell.Address(False, False)
      Exit For
   End If
Next cell

 

[jolivanes]

Jeff got you going already but this is a different approach.

Sub No_Loop_Required()
Dim search As String
search = Application.InputBox("Please Enter The Search Value", "Text String Required", , , , , 2)
MsgBox "Found the value at " & Range("E1:E30").Find(search, , , 1).Address(0, 0)
End Sub

FYI

 

[bigdan]

thanks a lot guys!