CODEWITHSUNDEEP
vbaexcel
dhruva_04
dhruva_04

Reputation: 141

Matching cell values in different sheets

so I am having a tough time here. I have two different spread sheets. Let's say Sheet1 has a column with data in the corresponding rows(A1,A2,A3..) as x, y, z...

Now I have another spread sheet(Sheet2) with the same set of data (x, y, z...) in one column say "A". In column "B" I have corresponding values say (1, 2, 3) such that

A1 = x, B1 = 1

I want my macro to read the values off the first column of Sheet1(i.e x, y or z) and then match it with the column data("A") of Sheet2 and hence, pick the corresponding values from the column "B" of Sheet2.

I have been trying for a while, but I am unable to figure out a way. And I am sorry for presenting it in such a haphazard manner. But someone please help? Here is mu attempted code..but this would work(if it does) only when the values in the column are ("2012-2013" etc..) I want it to be dynamic.

Sub CalC_stat()
Dim ws As Sheets
Set ws = ThisWorkbook.Sheets(Array("S1 Fuel Consumption", "EF_Stat", "Summary"))
Dim i As Integer, j As Integer

For j = 7 To 15 Step 4

Select Case ws(1).Range("A" & j).Value

Case "2012-2013"

For i = 1 To 4
 With ws(1).Shapes("Fuel " & i).ControlFormat

 Select Case .ListIndex

 Case 1
 ws(3).Range("B" & i).Value = Empty
 Case 2
 ws(3).Range("B" & i).Value = ws(2).Range("B3").Value
 Case 3
 ws(3).Range("B" & i).Value = ws(2).Range("C3").Value
 Case 4
 ws(3).Range("B" & i).Value = ws(2).Range("D3").Value

End Select
End With
Next i

Case "2013-2014"
 For i = 5 To 8
     With ws(1).Shapes("Fuel " & i).ControlFormat

    Select Case .ListIndex

    Case 1
    ws(3).Range("B" & i).Value = Empty
    Case 2
    ws(3).Range("B" & i).Value = ws(2).Range("B4").Value
    Case 3
    ws(3).Range("B" & i).Value = ws(2).Range("C4").Value
    Case 4
    ws(3).Range("B" & i).Value = ws(2).Range("D4").Value

End Select
End With
Next i

Case "2014-2015"
    ' and so on..

Upvotes: 0

Views: 197

Answers (1)

StandardDeviation
StandardDeviation

Reputation: 140

One VBA way to do it:

Sub test()

    dim a as Long, b as Long, c as Long

    For a = 1 to 1048576 'last row of your excel sheet
        If IsEmpty(Sheet1.Range("A" & a)) Then Exit For
    Next a

    For b = 1 to a
        For c = 1 to a
            If Sheet1.Range("A" & b) = Sheet2.Range("A" & c) Then 
               Sheet1.Range("B" & b) = Sheet2.Range("B" & c)
               Exit For
            End If
        Next c
    Next b

End Sub

Upvotes: 1

Related Questions