PLEASE HELP - MACRO NEEDED

[K]

I have data in sheet 1 (see below)
ROW A B C ------col (sheet 1)
1 CODE VALUE CODE_2
2 SS 10 GGG
3 TT 20 SSS
4 VV 30 TTT
5 ZZ 40 GGG
6 XX 50 GGG
7 RR 60 TTT
8 HH 70 TTT
9 JJ 80 GGG
10 KK 90 SSS
11 PP 100 TTT

in sheet 2 I have table and in cell B1 , C1 and D1 I have same Codes
as headings which are used in column C of sheet 1 (see below)

ROW A B C D----col (sheet 2)
1 CODE GGG SSS TTT
2 TT
3 SS
4 KK
5 PP

I want macro to check that if "GGG" which is in cell B1 of sheet 2 and
"TT" which is in cell A2 of sheet 2 come in same row in range "A2:C11"
of sheet 1 then get the value from column B of sheet 1 of that row and
put in cell B2 of sheet 2. And do same with other codes which are
"SS , KK , PP" in column A of sheet 2 and again same prossess with
codes "SSS and TTT". Basically i want macro to check 3 digit codes in
row 1 of column B to C and 2 digit codes from column A of sheet 2 into
sheet 3 and when 3 digit code and 2 digit code come in same row in col
A and C of sheet 3 then pick value form col B of that row and put in
sheet 2 against the codes.

macro should produce the result something like this (see below)

ROW A B C D----col (sheet 2)
1 CODE GGG SSS TTT
2 TT 0 20 0
3 SS 10 0 0
4 KK 0 90 0
5 PP 0 0 100

 

[Joel]

This code will work

Sub lookup_data()
'fill sheet 2 with zeroes
With Sheets("Sheet2")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("B2" & LastRow).Value = 0
End With
With Sheets("Sheet1")
RowCount = 2
Do While .Range("A" & RowCount) <> ""
Code = .Range("A" & RowCount)
Data = .Range("B" & RowCount)
Code_2 = .Range("C" & RowCount)
With Sheets("Sheet2")
'check if code_2 is in row1
Set R1 = .Rows(1).Find(what:=Code_2, _
LookIn:=xlValues, lookat:=xlWhole)
If Not R1 Is Nothing Then
'check if code 1 is in column A
Set C1 = .Columns("A:A").Find(what:=Code, _
LookIn:=xlValues, lookat:=xlWhole)
If Not C1 Is Nothing Then
.Cells(C1.Row, R1.Column) = Data
End If
End If
End With
RowCount = RowCount + 1
Loop
End With
End Sub

 

[K]

This code will work

Sub lookup_data()
'fill sheet 2 with zeroes
With Sheets("Sheet2")
   LastRow = .Range("A" & Rows.Count).End(xlUp).Row
   .Range("B2" & LastRow).Value = 0
End With
With Sheets("Sheet1")
   RowCount = 2
   Do While .Range("A" & RowCount) <> ""
      Code = .Range("A" & RowCount)
      Data = .Range("B" & RowCount)
      Code_2 = .Range("C" & RowCount)
      With Sheets("Sheet2")
         'check if code_2 is in row1
         Set R1 = .Rows(1).Find(what:=Code_2, _
            LookIn:=xlValues, lookat:=xlWhole)
         If Not R1 Is Nothing Then
            'check if code 1 is in column A
            Set C1 = .Columns("A:A").Find(what:=Code, _
               LookIn:=xlValues, lookat:=xlWhole)
            If Not C1 Is Nothing Then
               .Cells(C1.Row, R1.Column) = Data
            End If
         End If
      End With
      RowCount = RowCount + 1
   Loop
End With
End Sub










- Show quoted text -

Thanks Joel ! your macro works perfectly fine