Using 'For Each' loop in Ms Excel VBA

[umgbemena]

For each Match in the workbook,I wish to add the value in the B cell of worksheet1 and the value in the C cell of worksheet2 and display the result if less than 100. This same process is repeated for worksheet1 and worksheet3 and on until worksheet10. The iteration now starts from worksheet2 and worksheet3 and so on until worksheet2 and worksheet10. Iteration continues beginning from worksheet3 and the value in C cell of worksheet4...until worksheet3 and C cell value of worksheet10. The last iteration for match1 would be the use of the value in the B cell of sheet9 and the value in C cell of sheet10. This will happen before the loop goes to Match2 and executes the above iteration process all over again until the last Match (Match10) is looped through with the corresponding iteration through the cells as above and finally displays all values less than 100 through the message box.

 

[JLGWhiz]

A lot of detail not specified but with assumptions you should be able to work with the code below.

Sub t()
Dim i As Long, c As Range, fn As Range, adr As String
    For i = 2 To 10
        With Sheets(1)
            For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
                Set fn = Sheets(i).Range("A:A").Find(c.Value, , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    adr = fn.Address
                    Do
                        If fn.Offset(, 1).Value < 100 Then
                            .Cells(c.Row, Columns.Count).End(xlToLeft).Offset(, 1) = fn.Offset(, 1).Value
                        End If
                        Set fn = Sheets(i).Range("A:A").FindNext(fn)
                    Loop While fn.Address <> adr
                End If
            Next
        End With
    Next
End Sub

 

[umgbemena]

For each Match in the workbook,I wish to add the value in the B cell of worksheet1 and the value in the C cell of worksheet2 and display the result if less than 100. This same process is repeated for worksheet1 and worksheet3 and on until worksheet10. The iteration now starts from worksheet2 and worksheet3 and so on until worksheet2 and worksheet10. Iteration continues beginning from worksheet3 and the value in C cell of worksheet4...until worksheet3 and C cell value of worksheet10. The last iteration for match1 would be the use of the value in the B cell of sheet9 and the value in C cell of sheet10. This will happen before the loop goes to Match2 and executes the above iteration process all over again until the last Match (Match10) is looped through with the corresponding iteration through the cells as above and finally displays all values less than 100 through the message box.

I could provide more details if you wish. What more details do you need.

 

[JLGWhiz]

None, if the code worked. If it did not work, tell me what it did do, error message, line of code highlighted when Debug button is clicked, etc.

 

[umgbemena]

None, if the code worked. If it did not work, tell me what it did do, error message, line of code highlighted when Debug button is clicked, etc.

I copied and pasted the code in my Ms Excel VBA editor and then clicked on "Run" but didn't notice any response. See attached image. Thanks.

 

[JLGWhiz]

Test set up, based on Post #1 info produced additional entries in columns D, E of sheet 1 for me. I did not use the full 10 sheets for testing. For the code to produce results, sheet 1 must have data in column A. Sheets 2 thru 10 must have similar data in column A and numerical data in columns B and C.. It was not specified if duplicates would be in column A but the code provides for there being duplicates. It was also not specified if the data displayed on each sheet should be designated by its source sheet. And other details that migh make the data match more meaningful, but you might not need all the frills, depending on what your end objective is.

 

[umgbemena]

Test set up, based on Post #1 info produced additional entries in columns D, E of sheet 1 for me. I did not use the full 10 sheets for testing. For the code to produce results, sheet 1 must have data in column A. Sheets 2 thru 10 must have similar data in column A and numerical data in columns B and C.. It was not specified if duplicates would be in column A but the code provides for there being duplicates. It was also not specified if the data displayed on each sheet should be designated by its source sheet. And other details that migh make the data match more meaningful, but you might not need all the frills, depending on what your end objective is.

Yes. The data in column(sheet1) are duplicated on columns A of other sheets and data displayed on each sheet should be designated by its source sheet too. The purpose of the excel analysis is actually to find arbitrage opportunities using data from bookies. The main calculations ought to be 1/the numerical value in cell B of one sheet + 1/the numerical value in cell C of another sheet * 100. If value derived from calculation is less than 100, a message box displays the result. You could adjust the necessary portion of the code and test again. Thanks a lot.

 

[umgbemena]

Test set up, based on Post #1 info produced additional entries in columns D, E of sheet 1 for me. I did not use the full 10 sheets for testing. For the code to produce results, sheet 1 must have data in column A. Sheets 2 thru 10 must have similar data in column A and numerical data in columns B and C.. It was not specified if duplicates would be in column A but the code provides for there being duplicates. It was also not specified if the data displayed on each sheet should be designated by its source sheet. And other details that migh make the data match more meaningful, but you might not need all the frills, depending on what your end objective is.

I ran the code once again and got the attached results on sheet 1 of the workbook. Could not interpret the meaning of the results. The first row (match1) had no data in them at all.

 

[JLGWhiz]

I think I am going to pass on this one. Maybe someone else who has a better understanding of what you are trying to do will jump in and offer a solution.
Regards, JLG

 

[umgbemena]

I think I am going to pass on this one. Maybe someone else who has a better understanding of what you are trying to do will jump in and offer a solution.
Regards, JLG

Thanks a lot. You've been of great help.