Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through Variables
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
use different string variables in FOR loop | Excel Programming | |||
Loop thru variables | Excel Programming | |||
evaluate and variables and loop... | Excel Programming | |||
Non Static Variables in a For...Next Loop | Excel Programming | |||
Write variables from a loop, once only | Excel Programming |