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

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:D" & 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" wrote:

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

On 13 Apr, 10:25, Joel wrote:
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:D" & 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" wrote:
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- Hide quoted text -

- Show quoted text -

Thanks Joel ! your macro works perfectly fine