Loop through Variables

[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

 

[Dave Peterson]

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.

 

[Gary''s Student]

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

 

[Chad]

Thanks very much for the quick response. I will have a look at this
and see if I can get it working. Thanks also for the tips and
advice. Will take it on board this nearly sinking ship. Take care

Chad

 

[Dave Peterson]

Is that you, Captain Morgan????

Thanks very much for the quick response. I will have a look at this
and see if I can get it working. Thanks also for the tips and
advice. Will take it on board this nearly sinking ship. Take care

Chad