Need Help

[MariPip]

Hello!
I am trying to come up with a code that does the following things:
I have two sheets, sheet "RESUMO" and sheet "Gran.Solos NLT 10491".
Each sheet has a cell named "Sample" next to which the sample number is written.
In sheet resumo there is a column full of sample numbers, and I need for the other sheet to find the row with the same sample number it has and copy the information on the cell from that row and column G, and paste it on cell B4 from the other sheet.

Private Sub Worksheet_Change(ByVal target As Range)
Dim sh1 As Worksheet, sh2 As Worksheet, LR As Long
Set sh1 = Sheets("RESUMO")
Set sh2 = Sheets("Gran.Solos NLT 10491")
LR = sh1.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
If Not Intersect(target, Range("C27:C" & LR)) Is Nothing Then
If target.Value = sh2.Cells("B4").Value Then
sh2.Cells("B5").Value = sh1.Cells("i,G").Value
Else
sh2.Cells("B5").Value = ""
End If
End If
End Sub

This code does nothing, can anyone help me fix it? Thanks

 

[AlphaFrog]

I don't follow what you're trying to achieve from the description and code.

I did notice this is the wrong syntax and the variable i is not defined.
sh2.Cells("B5").Value = sh1.Cells("i,G").Value

This is the correct syntax but i is still not defined (Not sure what want to do).
sh2.Cells("B5").Value = sh1.Cells(i, "G").Value

Maybe this (a guess).
sh2.Cells("B5").Value = Target.Offset(0,4).Value 'Column G

Why are you using the Worksheet_Change event procedure and which worksheet is this code for?

 

[MariPip]

First of all, thank you for your answer, although it doesn't seam to work
What I want to do is create a code that does this:
in sheet "Gran.Solos NLT 10491" there will be a number next to "Amostra" (which means Sample). In Sheet "RESUMO" (which means summary) there is also a column for the "Amostra" (sample) and it will compile the data from several samples. I need for certain cells in sheet "Gran.Solos NLT 10491" to have their values depend on other cells from sheet "RESUMO" , which are under several columns.
The row in which the same sample number is inserted is the one from which the cells from the other Worksheet will pick up the data.
i.e.:
If the sample number from sheet "Gran.Solos NLT 10491" is 5, and in sheet "RESUMO" there is a column with samples "1,3,7,5,8..." the code will recognize the 5, check its row and then copy the values under certain columns.
I have the code under worksheet RESUMO, but I'm not even sure that's the right way.
If I have not made myself clear in anyway please do tell me
Thank you again
Hope you can help me !

 

[AlphaFrog]

What are the Amostra column letters on both sheets?

This code would go in the Gran.Solos NLT 10491 worksheet code module. It triggers when you make a change to the Amostra column on Gran.Solos NLT 10491

 

[MariPip]

On sheet RESUMO it is column C (starting at row 27) and on Gran.Solos NLT 10491 it is on cell B4 (the number of the sample)

 

[AlphaFrog]

So you change Gran.Solos NLT 10491!B4 and you want to look up the sample number in RESUMO!C27:C? and return the value from column G of the match? Is that correct?

 

[MariPip]

So you change Gran.Solos NLT 10491!B4 and you want to look up the sample number in RESUMO!C27:C? and (...)

Yes that is exactly it!

 

[AlphaFrog]

You could use just a VLOOKUP formula instead of code...

=VLOOKUP(B4, RESUMO!C27:G1000, 5, 0)

 

[MariPip]

You could use just a VLOOKUP formula instead of code...

=VLOOKUP(B4, RESUMO!C27:G1000, 5, 0)

Where should I insert the formula?

 

[AlphaFrog]

In the cell where you want the result.