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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through Variables
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through Variables
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through Variables
Is that you, Captain Morgan????
Chad wrote: 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 |
Reply |
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 |