Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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********** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
need help with loop ? again | Excel Discussion (Misc queries) | |||
do..loop | Excel Discussion (Misc queries) | |||
Do Loop | Excel Discussion (Misc queries) | |||
Help with Do...Loop | Excel Discussion (Misc queries) |