none
loop in a loop with excel RRS feed

  • Question

  • I have different values from cell A1 to A100 in excel file-A and values from cell A1 to A100 in excel file-B. What I want is if Cell A1 of file-A is equal to any of the Cells A1 to A100 then do something. Then go to A2 of file-A and check the same.... till A100 of file-A.
    Imports Microsoft.Office.Interop.Excel
    
    Module Module1
    
        Sub Main()
            
            Dim FileExistsCashBook As String
            Dim FileExistsSales As String
           
            FileExistsCashBook = "C:\Users\Abdus Sattar\Google Drive\Cash Book.xls"
            FileExistsSales = "C:\Users\Abdus Sattar\Google Drive\Sales.xls"
            If System.IO.File.Exists(FileExistsCashBook) AndAlso System.IO.File.Exists(FileExistsSales) Then
                
                Dim filenameCashBook As String = "C:\Users\Abdus Sattar\Google Drive\Cash Book"
                Dim fileNameSales As String = "C:\Users\Abdus Sattar\Google Drive\Sales"
    
                Dim oExcel As Object
                oExcel = CreateObject("Excel.Application")
                oExcel.Workbooks.Open(filenameCashBook)
                Dim xlsApp = New Microsoft.Office.Interop.Excel.Application
                xlsApp.ScreenUpdating = False
                Dim xlsBook As Microsoft.Office.Interop.Excel.Workbook
                Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet
                xlsBook = oExcel.ActiveWorkbook
                oSheet = oExcel.Worksheets(1)
    
                oExcel.calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual
    
                Dim oExcel2 As Object
                oExcel2 = CreateObject("Excel.Application")
                oExcel2.workbooks.open(fileNameSales)
                Dim xlsApp2 = New Microsoft.Office.Interop.Excel.Application
                xlsApp2.ScreenUpdating = False
                Dim xlsBook2 As Microsoft.Office.Interop.Excel.Workbook
                Dim oSheet2 As Microsoft.Office.Interop.Excel.Worksheet
                xlsBook2 = oExcel2.ActiveWorkbook
                oSheet2 = oExcel2.Worksheets(1)
                oExcel2.calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual
    So far I have done this (created two excel files and opened it). Please Help

    Abdus Sattar

    Tuesday, January 5, 2016 2:18 PM

Answers

  • Hi Abdus,

    For this requirement, you could loop cells and using Range.Find method to find the corresponding value in specify range.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, January 7, 2016 2:27 AM
    Moderator

All replies

  • Hi Abdus Sattar,

    Visual Basic forum is aim to discuss and ask questions about the Visual Basic programming language. I will move your thread to Excel for Developer forum for dedicated help.

    Best Regards,
    Herro Wong


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, January 6, 2016 1:57 AM
  • thanks:)

    Abdus Sattar

    Wednesday, January 6, 2016 2:34 PM
  • Hi Abdus,

    For this requirement, you could loop cells and using Range.Find method to find the corresponding value in specify range.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, January 7, 2016 2:27 AM
    Moderator