Help to get Cell value with multiple conditions

[nhnn1986]

Hi all

I need help to take value of Cell that:
with j = 2 to lastrow
if Cell(J;G).value = Cell(A2:A).value and Cell(i;B).value = "R101" then copy value in Cell(i;C) to Hj
if Cell(J;G).value = Cell(A2:A).value and Cell(i;B).value = "R102" then copy value in Cell(i;C) to Ij
if Cell(J;G).value = Cell(A2:A).value and Cell(i;B).value = "R103" then copy value in Cell(i;C) to Jj

This mean I want after run code, the result is:
H2 = 0, I2 = 0, J2 = 0
H3 = 5000, I3 = 6000, J3 = 7000
H4 = 10, I4 = 5, J4 = 20
..........

thanks./.

A	B	C	D	E	F	G	H	I	J
(1)	(2)	(3)	(4)	(5)	(6)	(7)	(8)	(9)	(10)
xxxx	R101	100				zzz
xxxx	R102	200				zzzz
xxxx	R103	500				yyyy
yyyy	R101	10				xxxx
yyyy	R102	5				.....
yyyy	R103	20
zzzz	R101	5000
zzzz	R102	6000
zzzz	R103	7000
.....	.....	....

<tbody>
</tbody>

 

[nhnn1986]

Sorry, it delete from G to I and the value still = 0

 

[nhnn1986]

Cross posted http://www.vbaexpress.com/forum/sho...py-cell-value-with-two-conditions-by-VBA-code

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Sorry i don't now This forum don't accept the question asked with another forum already

 

[Fluff]

We do not prohibit members from asking the same question elsewhere, we simply ask that you inform us by including a link to the other sites.

If your description on vbax is anything to go by, I'm not surprised that it's returning 0s as you have given a different description of what your data looks like.
Is the data you showed in post#1 accurate?

 

[nhnn1986]

We do not prohibit members from asking the same question elsewhere, we simply ask that you inform us by including a link to the other sites.

If your description on vbax is anything to go by, I'm not surprised that it's returning 0s as you have given a different description of what your data looks like.
Is the data you showed in post#1 accurate?

Yes, there's the same data.
I deleted column C,D before post in this forum because there's no meanning and value in column A too long so I rewrite to shorter as you see

 

[Fluff]

What about the R101 versus R-101?

 

[nhnn1986]

What about the R101 versus R-101?

Oh I rewrite to shoter, I think data in Cell "R-101" like "R101" so I type "R101" is more simple than type "R-101"

 

[Fluff]

It may be "simpler" but if that is not what your data is like it wont work, we need accurate information in order to help.
try

Sub LookupData()
   Dim rng As Range
   
   Set rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
   With Range("H2", Range("G" & Rows.Count).End(xlUp).Offset(, 1))
      .Resize(, 3).ClearContents
      .FormulaR1C1 = "=SUMPRODUCT((" & rng.Address(, , xlR1C1) & "=rc[-1])*(" & rng.Offset(, 1).Address(, , xlR1C1) & "=""R-101"")," & rng.Offset(, 2).Address(, , xlR1C1) & ")"
      .Offset(, 1).FormulaR1C1 = "=SUMPRODUCT((" & rng.Address(, , xlR1C1) & "=rc[-2])*(" & rng.Offset(, 1).Address(, , xlR1C1) & "=""R-102"")," & rng.Offset(, 2).Address(, , xlR1C1) & ")"
      .Offset(, 2).FormulaR1C1 = "=SUMPRODUCT((" & rng.Address(, , xlR1C1) & "=rc[-3])*(" & rng.Offset(, 1).Address(, , xlR1C1) & "=""R-103"")," & rng.Offset(, 2).Address(, , xlR1C1) & ")"
      .Resize(, 3).Value = .Resize(, 3).Value
   End With
End Sub

 

[nhnn1986]

Great @Fluff, code working.
Thanks a lot./.

 

[Fluff]

Glad to help & thanks for the feedback