Help with a Loop please
 

Hi,

I am not sure whether the solution exactly works for you. You can define a
named range on the 384 results by

Range(Cells(10,1),Cells(384,1)).name = "MyRange"


rngCount = Range("MyRange").Rows.count
J = 1
' Loop through the entire range. and the Named Range will take care of the
address of the cell.

For I = 1 to rngCount
val = Range("MyRange).Cells(I,col). value

If FindValueExists(val) then ' check whether it exists in another sheet

Range("MyRange").Rows(I).copy Destination:=Worksheets(index).Cells(J,6)
J = J + 1

End If

Next

Function FindValueExists(val as Integer) as boolean ' This method will match
the given integer value against the 8 values in other workbook

regards
Kamal

"Martin" wrote in message
...
I have 384 results in Column E, these need to be matched to values in 8
other Workbooks and these values then pasted to Column F. I've got as far
as
retrieving the first 48, but can't figure out how to get the next 48
(i.e.,
E50 - E97) and so on. Can this be done?

Thanks
Martin

*******Start Code***********
Sub MatchPredictions()
'CellToFind - Myrna Larson
Dim WS4 As Worksheet
Dim WS11 As Worksheet
Dim CellToFind As Range
Dim CellToCopy As Range
Dim o As Integer
Dim p As Integer

Workbooks.Open "M_TempResults" & o & "1Single.xls"
Workbooks.Open "M_PredictionResults.xls"

For o = 4 To 18 Step 2
Set WS4 = Workbooks("M_TempResults" & o & "1Single.xls").Sheets(1)
Set WS11 = Workbooks("M_PredictionResults.xls").Sheets(1)

For p = 2 To 49
Set CellToFind = WS11.Range("e" & p) ''''this is wrong, takes

''''e2 to e49 only

WS4.Activate
Range("B1:CX1").Select

Set CellToCopy = Selection.Find(what:=CellToFind, _
after:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

CellToCopy.Offset(3, 0).Copy _
Destination:=WS11.[f65536].End(xlUp).Offset(1, 0)
Set CellToFind = Nothing
Set CellToCopy = Nothing
Next p
Next o
End Sub
********End Code**********

Help with a Loop please
 

Kamal,

Thanks for responding so quickly, but I obviously didn't explain accurately
enough. The results contained in e2:e48 need to be matched to values in the
first workbook. The results contained in e50:e97, may be the same results
numerically as e2:e48, but these need to be matched to a different set of
values in the second workbook.

Am I missing something or should there be more to the function?

Thanks
Martin

Kamal wrote in message ...
Hi,

I am not sure whether the solution exactly works for you. You can define
a
named range on the 384 results by

Range(Cells(10,1),Cells(384,1)).name = "MyRange"


rngCount = Range("MyRange").Rows.count
J = 1
' Loop through the entire range. and the Named Range will take care of the
address of the cell.

For I = 1 to rngCount
val = Range("MyRange).Cells(I,col). value

If FindValueExists(val) then ' check whether it exists in another sheet

Range("MyRange").Rows(I).copy
Destination:=Worksheets(index).Cells(J,6)
J = J + 1

End If

Next

Function FindValueExists(val as Integer) as boolean ' This method will
match
the given integer value against the 8 values in other workbook

regards
Kamal

Help with a Loop please
 

Kamal,
Can you please repost Function FindValueExists as my newsreader appears only
to have the first line
Thanks
Martin
Kamal wrote in message ...
You can add few changes to the for loop and pass the excel sheet to the
method FindValueExists(sheet, val, indexOnDestSheet) might help you.

"Martin" wrote in message
...
Kamal,

Thanks for responding so quickly, but I obviously didn't explain
accurately
enough. The results contained in e2:e48 need to be matched to values in
the
first workbook. The results contained in e50:e97, may be the same results
numerically as e2:e48, but these need to be matched to a different set of
values in the second workbook.

Am I missing something or should there be more to the function?

Thanks
Martin

Kamal wrote in message ...
Hi,

I am not sure whether the solution exactly works for you. You can
define
a
named range on the 384 results by

Range(Cells(10,1),Cells(384,1)).name = "MyRange"


rngCount = Range("MyRange").Rows.count
J = 1
' Loop through the entire range. and the Named Range will take care of
the
address of the cell.

For I = 1 to rngCount
val = Range("MyRange).Cells(I,col). value

If FindValueExists(val) then ' check whether it exists in another
sheet

Range("MyRange").Rows(I).copy
Destination:=Worksheets(index).Cells(J,6)
J = J + 1

End If

Next

Function FindValueExists(val as Integer) as boolean ' This method will
match
the given integer value against the 8 values in other workbook

regards
Kamal