Define row or cell variables

matthew230

Board Regular
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

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Boller

Banned
Code:
``````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

MrExcel MVP
You may want to look at Find:

Code:
``````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:

Code:
``````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

Board Regular
You may want to look at Find:

Code:
``````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:

Code:
``````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

MrExcel MVP
Code:
``````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

Board Regular
Got it,

Now just to work out the rest !!

many thanks

Matt

Boller

Banned
Thank you very much for your invaluable help !!

matt

You're welcome.

Replies
3
Views
463
Replies
9
Views
4K
Replies
7
Views
5K
Replies
4
Views
560
Replies
9
Views
304

Forum statistics

Threads
1,191,070
Messages
5,984,455
Members
439,889
Latest member
mukundghayadar1983

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back