copy cell name

[Martin Wheeler]

xl2000

I want to find a cell name and then copy that name to a spreadsheet I am
putting together
Below is what I have so far. So I want to find the cell that "1" is in. If
that cell is, say "M18" I want to then copy "M18" to
Range("BB7").End(xlDown)) in the new spreadsheet.
Any help would be greatly appreciated.
Ta,
Martin
Public Sub FindOne(wks As Worksheet)
Application.ScreenUpdating = False
On Error Resume Next
With wks
Set Start = Cells.Find(what:="1", _
After:=.Range("M14"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=True)
End With
End Sub

 

[Martin Wheeler]

I have changed things around, having read through some earlier posts and now
have the following code which does not work. What I want is the column
value of "7" to be displayed in range("R20"). I am doing something wrong
with the object but can't see it.
Any help would be greatly appreciated.
Ta,
Martin
Public Sub Col()
Dim MyValue As Range
Dim MyColumn As Long

Set MyValue = Range("M7")
MyColumn = Range("myvalue").Column

Range("R20").Value = MyColumn
End Sub

 

[Martin Wheeler]

I have solved the problem so no need to reply.
Ta,
Martin