C
Chad
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
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