Looping through 2 sheets

[matt_leonard]

Hi,

I have 2 workbooks with some data on. In column B is a ref number. Basically if the ref number is on todays workbook, i need it to pull across the data in 3 columns.

I am trying to do it with 2 loops, but dosent appear to work. It doesnt seem to like the 2nd loop? Can someone please advise me where im going wrong? I am trying the following

If WorkbookExists(Format(Now, "yyyymmdd") - 1 & " Typhoon Pre-allocation.xls") Then ' checks that the events sheet has been exported from GICAD
'Workbooks(Format(Now, "yyyymmdd") & " Typhoon Pre-allocation.xls").Activate

For i = 2 To usedrow
value1 = Cells(i, 2).value
Workbooks(Format(Now, "yyyymmdd") - 1 & " Typhoon Pre-allocation.xls").Activate
usedrow2 = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
For a = 2 To usedrow2
If value1 = Cells(a, 2).value Then
match = True
comment1 = Cells(a, 23).value
comment2 = Cells(a, 24).value
comment3 = Cells(a, 25).value
Exit For
Else
match = False
Next a
If match = True Then
transfer.Activate
Cells(i, 23).value = comment1
Cells(i, 24).value = comment2
Cells(i, 25).value = comment3
Else
Next i

End If

thank you

 

[Colin Legg]

Hi Matt,

If I was going to do this then I would use VLOOKUP() formulas, either manually or automated from VBA.

Does that option sound viable to you?

 

[pbornemeier]

Some of the If..Then and For..Next structures crossed and parts them were missing. Review this link for info about indenting code, it helps coders to keep track of blocks of code to prevent those problems: http://en.wikipedia.org/wiki/Indent_style

Modifying the structure of your code a bit may give you the result you want:

    If workbookexists(Format(Now, "yyyymmdd") - 1 & " Typhoon Pre-allocation.xls") Then ' checks that the events sheet has been exported from GICAD
        Workbooks(Format(Now, "yyyymmdd") & " Typhoon Pre-allocation.xls").Activate
        For i = 2 To usedrow
            value1 = Cells(i, 2).Value
            Workbooks(Format(Now, "yyyymmdd") - 1 & " Typhoon Pre-allocation.xls").Activate
            usedrow2 = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
            For a = 2 To usedrow2
                If value1 = Cells(a, 2).Value Then
                    match = True
                    comment1 = Cells(a, 23).Value
                    comment2 = Cells(a, 24).Value
                    comment3 = Cells(a, 25).Value
                    Exit For
                Else
                    match = False
                End If
            Next a
            If match = True Then
                transfer.Activate
                Cells(i, 23).Value = comment1
                Cells(i, 24).Value = comment2
                Cells(i, 25).Value = comment3
            End If
        Next i
    End If

But it may take some time since it will have to do RowCount1 * RowCount2 comparisons. I am not sure what transfer.activate does

This layout may be a little more efficient since it loops through once and does a find for each row:

Sub CopyData()

    Dim dfound
    Dim i As Long
    Dim lngLastDataRow As Long
    Dim booYesterdayOpen As Boolean

    booYesterdayOpen = False
    For i = 1 To Windows.Count
        If Windows(i).Caption = Format(Now, "yyyymmdd") - 1 & " Typhoon Pre-allocation.xls" Then booYesterdayOpen = True:         Exit For
    Next
    
    If booYesterdayOpen = True Then ' checks that the events sheet has been exported from GICAD and is open
    
        Workbooks(Format(Now, "yyyymmdd") & " Typhoon Pre-allocation.xls").Activate
        lngLastDataRow = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row 'get last data row from today's file
    
        With Workbooks(Format(Now, "yyyymmdd") - 1 & " Typhoon Pre-allocation.xls")
            For i = 2 To lngLastDataRow
                Set dfound = .Worksheets(1).Columns(2).Find(Cells(i, 2).Value)
                If Not dfound Is Nothing Then
                    Cells(i, 23).Value = .Worksheets(1).Cells(dfound.Row, 23).Value
                    Cells(i, 24).Value = .Worksheets(1).Cells(dfound.Row, 24).Value
                    Cells(i, 25).Value = .Worksheets(1).Cells(dfound.Row, 25).Value
                End If
            Next i
        End With
    Else
        MsgBox Format(Now, "yyyymmdd") - 1 & " Typhoon Pre-allocation.xls is not open.  Open it and try again."
    End If
    Set dfound = Nothing

End Sub

You may want to put the version you choose in the personal.xls file, so that it does not have to be added to the export file each day.