It's probably simple, but...
 

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.

It's probably simple, but...
 

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

--
Regards,
Tom Ogilvy


"kmea" wrote in message
. ..
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.

It's probably simple, but...
 

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


--
Don Guillett
SalesAid Software

"kmea" wrote in message
. ..
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.

It's probably simple, but...
 

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
"kmea" wrote in message
. ..
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.