PDA

View Full Version : VBA Macro lookup help needed



Dodsy
05-10-2012, 08:49 AM
Hi,

I have MS Project 2003 and need to add some text to a number to create a search criteria. e.g.:

I have a set of Dim Strings Ex1, Ex2, Ex3 etc. that contain the names of sites

I am doing a search with a macro to locate the site name in the project. this needs to be repeated 44 times looking for a different site name each time

So I have a little routine with a counter in it, what I want to do is take the text EX then add the counter to it, to give me (for example) EX1. I then want to use the contents of EX1 to do a search.

In MS Excel I would use the indirect command but its not available in MS project. I was thinking an array might do it , so maybe have Ex=Array("MANCHESTER","LIVERPOOL","LONDON") but I'm a nit of a numpty on VBA and cant work out how to reference the array. In my old programming days I would just define a 1 dimensional array then reference it by using the array name and (1) or (2) etc. but that doesn't seem to work

This is what I have that doesnt work, any help appreciated !

Sub newmacro2()

Dim sdcount As Integer
Dim ex1 As String
Dim ex2 As String
Dim ex3 As String

ex1 = "MANCHESTER"
ex2 = "LIVERPOOL"
ex3 = "LONDON"

For sdcount = 1 To 3
Find Field:="Name", Test:="contains", Value:="ex" & sdcount, Next:=True, MatchCase:=False
{in here are the various things I will do once I locate the item being searched for}
Next
End Sub