MariPip

New Member
Joined
Jul 16, 2014
Messages
29
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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?
 
Upvote 0
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 !
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top