Use Arrays:

http://www.cpearson.com/excel/VBAArrays.htm

for example:

Sub dural()
Dim MyVar(1 To 5) As String
For i = 1 To 5
MyVar(i) = Sheets("Control").Range("A" & i).Value
Next
End Sub

--
Gary''s Student - gsnu200852


"Chad" wrote:

Hi

I am trying to loop through a set of 5 assigned variables. I would
like to put a for loop or something which will make the below code
more efficient maily for my own learning. My attempts so far have
been poor. I would greatly appreciate any assistance in the right
direction.


Sub FindMyVal()

Dim vOurResult1 As Long
Dim vOurResult2 As Long
Dim vOurResult3 As Long
Dim vOurResult4 As Long
Dim vOurResult5 As Long
Dim COL As Long
Dim MyVar1 As String
Dim Myvar2 As String
Dim Myvar3 As String
Dim Myvar4 As String
Dim Myvar5 As String

MyVar1 = Sheets("Control").Range("A1").Value
Myvar2 = Sheets("Control").Range("A2").Value
Myvar4 = Sheets("Control").Range("A3").Value
Myvar5 = Sheets("Control").Range("A4").Value
vOurResult3 = vOurResult1 + vOurResult2

With Sheets("Source").Range("B:C")
vOurResult1 = .Find(What:=MyVar1, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,
_
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 1)

vOurResult2 = .Find(What:=Myvar2, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,
_
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 1)
'Sum the results of the two Variables and place at the end
of Row 7

Sheets("Data").Select
vOurResult3 = vOurResult1 + vOurResult2
COL = Sheets("Data").Cells(7, 1).End(xlToRight).Column + 1
Sheets("Data").Range(Cells(7, COL), Cells(7, COL)).Value =
vOurResult3

vOurResult4 = .Find(What:=Myvar4, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,
_
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 1)
Sheets("Data").Range(Cells(8, COL), Cells(8, COL)).Value =
vOurResult4

vOurResult5 = .Find(What:=Myvar5, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,
_
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 1)
Sheets("Data").Range(Cells(9, COL), Cells(9, COL)).Value =
vOurResult5

End With
End Sub

I was trying something like the following which was not working as I
expected.

For i = 1 to 5

With Sheets("Source").Range("B:C")
vOurResult(i) = .Find(What:=MyVar(i), After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,
_
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 1)
End With
next i


Thanks and Regards

Chad