Loop through Variables
Maybe...
Option Explicit
Sub FindMyVal()
Dim vOurResult() As Long
Dim FoundCell As Range
Dim myVar As Variant
Dim iCtr As Long
Dim oCol As Long
'myVar becomes a 4 row by 1 column array/matrix.
myVar = Worksheets("Control").Range("A1:A4").Value
'make the results have as many elements as myvar has rows
ReDim vOurResult(LBound(myVar, 1) To UBound(myVar, 1))
With Worksheets("Source").Range("B:C")
'loop through the 4 rows
For iCtr = LBound(myVar, 1) To UBound(myVar, 1)
Set FoundCell = .Find(What:=myVar(iCtr), _
After:=.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
'what should happen to vourresult?
Else
If IsNumeric(FoundCell.Offset(0, 1).Value) Then
vOurResult(iCtr) = FoundCell.Offset(0, 1).Value
Else
'what should happen here?
End If
End If
Next iCtr
End With
'I'm not sure I got this portion right...
With Worksheets("data")
oCol = .Cells(7, 1).End(xlToRight).Column + 1
.Cells(7, oCol).Value = vOurResult(1) + vOurResult(2)
.Cells(8, oCol).Value = vOurResult(3)
.Cells(9, oCol).Value = vOurResult(4)
End With
End Sub
=======
Just some notes...
VBA doesn't allow you to loop through variables like
for i = 1 to 4
myVar&i = "something"
next i
But it does allow you to use arrays that you can loop through.
And this kind of syntax may work--or may be a difficult bug to find. It'll
depend on where the code is located.
Sheets("Data").Range(Cells(9, COL), Cells(9, COL)).Value = "something"
You'll want to qualify the all the range references (including Cells()):
with worksheets("Data")
.range(.cells(9,col),.cells(9,col)).value = "something"
end with
or
workSheets("Data").Range(worksheets("Data").Cells( 9, COL), _
worksheets("Data").Cells(9, COL)).Value = "something"
The leading dots in that first expression mean that they apply to the object in
the previous With statement -- in this case, the Data worksheet.
And there is no reason to use
.range(.cells(), .cells()).value ...
when you're just refering to a single cell.
.cells(9,col).value ...
is sufficient.
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
--
Dave Peterson
|