PDA

View Full Version : Registration of two parts



marreco
03-05-2012, 03:12 PM
Hi

Personally I have a file that has to work in two parts.

When a part is used, the data goes to the worksheet called "Base".
I made a macro to perform the first part, so far so perefeito!

But ...

As for "Part 2", I would like a code to analyze the cell "E3", the worksheet called "Part 2", go to the spreadsheet "Base", if found, complete the registration.

Thank you

Bob Phillips
03-05-2012, 04:56 PM
Try this worksheeet event code



Private Sub Worksheet_Change(ByVal Target As Range)
Dim matchRow As Long

Select Case Target.Address

Case "$E$9", "$E$11", "$E$13"

matchRow = Application.Match(Me.Range("E3").Value, Worksheets("Base").Columns("A"), 0)
Worksheets("Base").Cells(matchRow, Target.Row \ 2).Value = Target.Value
End Select
End Sub

marreco
03-05-2012, 05:19 PM
Very good, I loved it, his ability is fantastic.:rofl:

He was very grateful for helping me.:clap:

Thank you very much!:thumb

marreco
03-06-2012, 12:15 PM
I'd like to change this code for button.

When I type the data he plays for plnilha but when I turn out the form, in my spreadsheet also disappears.

Can you help me?

Bob Phillips
03-06-2012, 06:03 PM
I have absolutely no idea what you are saying.

marreco
03-07-2012, 02:59 AM
Hi.
I need something like ...

Sub Teste()
Dim matchRow As Long

Select Case Target.Address

Case "$E$9", "$E$11", "$E$13"

matchRow = Application.Match(Me.Range("E3").Value, Worksheets("Base").Columns("A"), 0)
Worksheets("Base").Cells(matchRow, Target.Row \ 2).Value = Target.Value
End Select
End Sub

But I can not change the rest of the code.
Thank you

Bob Phillips
03-07-2012, 03:39 AM
If you look at the original code, you will see that Target, which is the cell being changed, is passed as a parameter in the event procedure. Thus you can use it in the code.

In your amended code, you are not passing a Target parameter. This is not surprising if you are calling this from a button, but it means you cann use Target in the code. You have to workout which cell you need to work upon, is it a particular cell, the activecell, or what?