Hi,


Trying to create a macro that will take values in column A on m
"audit" sheet and compare them to 'myRng' on the "Rows" sheet
returning the value of the column the value is found in. How would
implement an if-then statement to keep the macro from erroring when th
value is not found in 'myRng'?
So far I've come up with:


Public Sub test()
Dim num As Integer
Dim mynum As Range
Dim cell As Range
Dim column As Integer

Worksheets("audit").Select

Set mynum = Range(Range("a2"), Range("a2").End(xlDown))


For Each cell In mynum
num = cell.Value

Worksheets("rows").Activate
Cells.Find(num).Activate
column = ActiveCell.column
Worksheets("audit").Select
cell.Offset(0, 1) = column
End If

End Sub

Appreciate the help.

Mjac

--
mjack00
-----------------------------------------------------------------------
mjack003's Profile: http://www.excelforum.com/member.php...nfo&userid=514
View this thread: http://www.excelforum.com/showthread.php?threadid=46844

Im getting a "Object variable or object variable not set" error on th
line "cells.find(num).activate". How can I get around this

--
mjack00
-----------------------------------------------------------------------
mjack003's Profile: http://www.excelforum.com/member.php...nfo&userid=514
View this thread: http://www.excelforum.com/showthread.php?threadid=46844

Public Sub testy()
Dim num As Long
Dim mynum As Range
Dim cell As Range
Dim oFind As Range
Dim iCol As Long

Worksheets("audit").Select
Set mynum = Range(Range("a2"), Range("a2").End(xlDown))

For Each cell In mynum
Set oFind = Nothing
Set oFind = Worksheets("rows").Cells.Find(cell.Value)
If Not oFind Is Nothing Then
cell.Offset(0, 1) = oFind.column
End If
Next cell

End Sub


--
HTH

Bob Phillips

"mjack003" wrote in
message ...

Im getting a "Object variable or object variable not set" error on the
line "cells.find(num).activate". How can I get around this?


--
mjack003
------------------------------------------------------------------------
mjack003's Profile:
http://www.excelforum.com/member.php...fo&userid=5141
View this thread: http://www.excelforum.com/showthread...hreadid=468447

This is one interpretation:

Sub XYZ()
Dim rng As Range
Dim c As Range, cc As Range
Dim ws As Worksheet, ws2 As Worksheet

Set ws = Sheets("audit")
Set ws2 = Sheets("rows")
Set rng = ws.Range(ws.Range("A2"), ws.Range("A2").End(xlDown))
For Each c In rng.Cells
Set cc = ws2.UsedRange.Find(c.Value, LookAt:=xlWhole)
If Not cc Is Nothing Then
c(1, 2) = cc.column
Set cc = Nothing
End If
Next
End Sub

Regards,
Greg

"mjack003" wrote:


Hi,


Trying to create a macro that will take values in column A on my
"audit" sheet and compare them to 'myRng' on the "Rows" sheet,
returning the value of the column the value is found in. How would I
implement an if-then statement to keep the macro from erroring when the
value is not found in 'myRng'?
So far I've come up with:


Public Sub test()
Dim num As Integer
Dim mynum As Range
Dim cell As Range
Dim column As Integer

Worksheets("audit").Select

Set mynum = Range(Range("a2"), Range("a2").End(xlDown))


For Each cell In mynum
num = cell.Value

Worksheets("rows").Activate
Cells.Find(num).Activate
column = ActiveCell.column
Worksheets("audit").Select
cell.Offset(0, 1) = column
End If

End Sub

Appreciate the help.

Mjack


--
mjack003
------------------------------------------------------------------------
mjack003's Profile: http://www.excelforum.com/member.php...fo&userid=5141
View this thread: http://www.excelforum.com/showthread...hreadid=468447