Thread: copy data from 2 sheets

Hope the title is accurate

I got presented with this code at work that doesn't seem to work as it should, the author has long since quit the company and now im stuck with it

Option Explicit

Public Sub Stat()

Dim X As Range, Y As Range
Dim A As Variant, B As Variant, C As Variant, D As Variant, E As Variant, F As Variant

For Each X In Workbooks("stat3_syd_q1.csv").Worksheets("stat3_syd_q1").Range("B1", "B65536")
    If Not X.Offset(0, 3).Value = "" Then
        If Not X.Value = "Kommunenr." Then
            For Each Y In Workbooks("stat3_SYD07.csv").Worksheets("stat3_SYD07").Range("B1", "B65536")
                If X = Y Then
                    If Not Y.Offset(0, 6).Value = "" Then
                        If Y.Offset(0, 1).Value = X.Offset(0, 1).Value Then
                            A = Y.Offset(0, 6).Value
                            B = Y.Offset(0, 8).Value
                            C = X.Offset(0, 6).Value
                            D = X.Offset(0, 8).Value
                            If Y.Offset(0, 2).Value = "---------" Then
                                Y.Offset(0, 2).Value = X.Offset(0, 2).Value
                            Else
                                Y.Offset(0, 2).Value = Y.Offset(0, 2).Value + X.Offset(0, 2).Value
                            End If
                            If Y.Offset(0, 3).Value = "---------" Then
                                Y.Offset(0, 3).Value = X.Offset(0, 3).Value
                            Else
                                Y.Offset(0, 3).Value = Y.Offset(0, 3).Value + X.Offset(0, 3).Value
                            End If
                            If Y.Offset(0, 4).Value = "---------" Then
                                Y.Offset(0, 4).Value = X.Offset(0, 4).Value
                            Else
                                Y.Offset(0, 4).Value = Y.Offset(0, 4).Value + X.Offset(0, 4).Value
                            End If
                            If Y.Offset(0, 5).Value = "---------" Then
                                Y.Offset(0, 5).Value = X.Offset(0, 5).Value
                            Else
                                Y.Offset(0, 5).Value = Y.Offset(0, 5).Value + X.Offset(0, 5).Value
                            End If
                            If Y.Offset(0, 6).Value = "---------" Then
                                Y.Offset(0, 6).Value = X.Offset(0, 6).Value
                            Else
                                Y.Offset(0, 6).Value = Y.Offset(0, 6).Value + X.Offset(0, 6).Value
                            End If
                            E = Y.Offset(0, 6).Value
                            F = ((A * B) + (C * D)) / E
                            If Y.Offset(0, 7).Value = "---------" Then
                                Y.Offset(0, 7).Value = X.Offset(0, 7).Value
                            Else
                                Y.Offset(0, 7).Value = Y.Offset(0, 7).Value + X.Offset(0, 7).Value
                            End If
                            Y.Offset(0, 8).Value = F
                        End If
                    End If
                End If
            Next
        End If
    End If
Next

End Sub

I only have a little experience with this kind, but from what i can tell its supposed to look for information in the stat3_syd_q1.csv and stat3_SYD07.csv that match and then enter into the sheet i have open.

The problem is it only checks to see if "Kommunenr." is true but it should also make sure that another number is the same, which is called "Lovkode"

hi, Genzo, the code actually takes data from sheet ("stat3_syd_q1") column B and looks for matches in sheet ("stat3_SYD07") by looping each cell in column B. When match is found there are 5 variables that are calculated to calculate the 6 - F variable which is put 8 columns to the right from column B on sheet ("stat3_SYD07") in the mached value row.

If you can provide sample workbook showing original data and result you need to obtain, we could look into it in a bit more detail

you want to see the actual worksheets?

how do i post them the best way?

To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.