Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
Simple problem, simple formula, no FUNCTION ! | Excel Worksheet Functions | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) | |||
Make it more simple or intuitive to do simple things | Charts and Charting in Excel | |||
simple question, hopefully a simple answer! | Excel Programming |