It's probably simple, but...

[kmea]

If anyone has the time to write sample code for the following, I would
appreciate it.

I would like to create a VB script in Microsoft Excel 2000.
The script needs to:
1. Look on a worksheet (Named B) in a specific cell (B1) for a value.
2. Find that same value on a different worksheet (Named A) in column A.
3. Then I run another script (already created and working) against the cell
location found in step 2.
4. This is repeated 170 times exactly. Each time I repeat, the cell location
on worksheet B changes. (B1, B2, B3...B170)

I do not know VB (trying to learn by doing), so even something as small as a
missing Dim could mess me up. Thanks in advance if anyone has the time to
help.

 

[Tom Ogilvy]

Sub RunMeMany()
Dim cell as Range, rng as Range
Dim rng1 as Range, rng2 as Range
set rng = worksheets("B").Range("B1:B170")
Worksheets("A").Activate
set rng1 = Worksheets("A").Range("A1").Entirecolumn
for each cell in rng
res = Application.Match(cell.Value, _
rng1,0)
if not res is nothing then
set rng2 = rng1(res)
rng2.select
' your current code here
else
' color cell red if not found
cell.Interior.colorIndex = 3
end if
Next
End Sub

 

[Don Guillett]

this should get you going. Modify to suit


Sub lookfor()
For Each c In Sheets("sheet5").Range("b1:b5")
x = Sheets("sheet4").Range("a1:a21").Find(c).Address
MsgBox x
Next c
End Sub

 

[Tom Ogilvy]

didn't dim res, so:
Sub RunMeMany()
Dim cell As Range, rng As Range
Dim rng1 As Range, rng2 As Range
Dim res As Variant
Set rng = Worksheets("B").Range("B1:B170")
Worksheets("A").Activate
Set rng1 = Worksheets("A").Range("A1").EntireColumn
For Each cell In rng
res = Application.Match(cell.Value, _
rng1, 0)
If Not res Is Nothing Then
Set rng2 = rng1(res)
rng2.Select
' you current code here
Else
' color cell red if not found
cell.Interior.ColorIndex = 3
End If
Next
End Sub

also, if the data you are looking for is a date, then you should do this:

Sub RunMeMany()
Dim cell As Range, rng As Range
Dim rng1 As Range, rng2 As Range
Dim res As Variant
Set rng = Worksheets("B").Range("B1:B170")
Worksheets("A").Activate
Set rng1 = Worksheets("A").Range("A1").EntireColumn
For Each cell In rng
res = Application.Match(clng(cell.Value2), _
rng1, 0)
If Not res Is Nothing Then
Set rng2 = rng1(res)
rng2.Select
' you current code here
Else
' color cell red if not found
cell.Interior.ColorIndex = 3
End If
Next
End Sub