Reputation: 141
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
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