Nesting if then loop
 

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

Nesting if then loop
 

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

Nesting if then loop
 

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

Nesting if then loop
 

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