stepping through a range

  • Thread starter Thread starter Randy F
  • Start date Start date
R

Randy F

I am compairing 2 lists of names and if the name on list1
showes up on list2 i then copy cells from clm e & f next
to the name in list1, but i cant get my macro to step
through the range, or for example go from range a2 too a3

here is my macro
Sub wrk()

Dim nm As Range, of As Range

Set nm = Worksheets("Names").Range("a2")
Set of = Worksheets("Names").Range("b2")

Worksheets("Data").Activate
Range("e2").Select

For Each c In [A2:A200]
If c.Value Like (nm) Then
ActiveCell.Value = (of)

End If


Selection.Offset(1, 0).Select

Next

End Sub

Thanks
RDY
 
[ ] notation is slower than normal object references and as shown, would
cause evaluation twice in each loop


Dim nm as variant
Dim of as variant
Dim c as Range
With Worksheets("Names")
' go ahead and get the value of the cells
nm =.Range("A2").Value
of = .Range("B2").Value
End With
for each c in Worksheets("Data").Range("A2:A200")
' check column A and write to E
if c.value like nm then _
c.offset(0,5).Value = of
Next

Of course, if you have a lot of data, it might be worthwhile to apply an
autofilter to identify the cells meeting the criteria and then use special
cells to write to the corresponding cells in column E (for the visible
rows).


--
Regards,
Tom Ogilvy





Don Guillett said:
try this. No need to SELECT anything. Should work from anywhere.

For Each c In [Data!a2:a200]
if c=[names!a2] then c.value=[names!b2]
next



--
Don Guillett
SalesAid Software
Granite Shoals, TX
(e-mail address removed)
Randy F said:
I am compairing 2 lists of names and if the name on list1
showes up on list2 i then copy cells from clm e & f next
to the name in list1, but i cant get my macro to step
through the range, or for example go from range a2 too a3

here is my macro
Sub wrk()

Dim nm As Range, of As Range

Set nm = Worksheets("Names").Range("a2")
Set of = Worksheets("Names").Range("b2")

Worksheets("Data").Activate
Range("e2").Select

For Each c In [A2:A200]
If c.Value Like (nm) Then
ActiveCell.Value = (of)

End If


Selection.Offset(1, 0).Select

Next

End Sub

Thanks
RDY
Click to expand...
Click to expand...
 
Back
Top