Define row or cell variables

[matthew230]

Hi Guys,

I am completely self taught, and need a few pointers on learning a new technique.

I need to be able to look at an entire column and search for a value. Once the value is found, then I need to make that cell a variable.

I have got so far

Dim currentcell As Range

Sub test()
For Each cell In Range("a1:a10")
If cell.Value = "b" Then
MsgBox "found it"
cell.Select
currentcell = Range(cell)

End If
Next
MsgBox currentcell

End Sub

The currentcell = range(cell) is the line that doesn't work.

Can someone help with this ??

Many thanks,

Matt

 

[Boller]

Sub test()
Dim currentcell As Range
Set currentcell = [A:A].Find("b")
If currentcell Is Nothing Then
    MsgBox "Not found"
Else
    MsgBox "Found it"
    currentcell.Select
End If
End Sub

 

[Scott Huish]

You may want to look at Find:

Sub test()
Dim c As Range
Set c = Range("A1:A10").Find("b")
If c Is Nothing Then Exit Sub
MsgBox "Found it in cell " & c.Address
End Sub

If it finds it, it sets c to be the cell that it found it at. c contains a range object and is treated as such.

But using your loop, you can use a range object the same way:

Sub test()
Dim c As Range, d As Range
For Each d In Range("A1:A10")
    If d = "b" Then
        Set c = d
        Exit For
    End If
Next
If Not c Is Nothing Then MsgBox "Found it in cell " & c.Address(0, 0)
End Sub

 

[matthew230]

You may want to look at Find:

Sub test()
Dim c As Range
Set c = Range("A1:A10").Find("b")
If c Is Nothing Then Exit Sub
MsgBox "Found it in cell " & c.Address
End Sub

If it finds it, it sets c to be the cell that it found it at. c contains a range object and is treated as such.

But using your loop, you can use a range object the same way:

Sub test()
Dim c As Range, d As Range
For Each d In Range("A1:A10")
    If d = "b" Then
        Set c = d
        Exit For
    End If
Next
If Not c Is Nothing Then MsgBox "Found it in cell " & c.Address(0, 0)
End Sub

Hi,

That is great, it was the .address part I needed to work out.

One more question, how do I now go about selecting another cell relative to the one found. In this example, I then need to move the cell focus one row down and one column across.

Thank you very much for your invaluable help !!

matt

 

[Scott Huish]

Sub test()
Dim c As Range
Set c = Range("A1:A10").Find("b")
If c Is Nothing Then Exit Sub
c.Offset(1, 1).Select
End Sub

 

[matthew230]

Got it,

Now just to work out the rest !!

many thanks

Matt

 

[Boller]

Thank you very much for your invaluable help !!

matt

You're welcome.