Help with VBA to return values to given cells

[Gregm66]

Hi everyone,

I have a worksheet called "Fees Paid" that holds members names and other values, In cells "A through to J"

Then i use another worksheet called "MembersReport", on the worksheet i use Vlookup formulas to return matching values from "Fees Paid" worksheet to certain cells in the "MembersReport" worksheet.

Cell "B9" on the "MembersReport worksheet has a in cell dropdown list of members names, this is what triggers the vlookup to return matching values, the the following cells in the "MembersReport" worksheet.

Cell "B11" gets its value from Column "E" on the "Fees Paid" worksheet
Cell "B13" from column "A" on the "Fees Paid" Worksheet
Cell "F9" from column "B" "Fees Paid" worksheet
Cell "F11" from column "F" fees Paid worksheet
Cell "F13" from Cell 'K2" on the Fees Paid worksheet, this one works on a date value. That being the date value that appears in Cell "K2"...

What i would like to do is to use VBA to acheive this instead on using formulas in each cell.

Thanks in advance for any help.

 

[DanteAmor]

Try this

Change "C:C" for the column with the names.
Put the code in sheet events.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Target.Address(0, 0) = "B9" Then
    Dim sh2 As Worksheet, f As Range
    Set sh2 = Sheets("Fees Paid")
    Set f = sh2.Range("[COLOR=#ff0000]C:C[/COLOR]").Find(Range("B9"), , xlValues, xlWhole)
    If Not f Is Nothing Then
      Range("B11") = sh2.Range("E" & f.Row)
      Range("B13") = sh2.Range("A" & f.Row)
      Range("F9") = sh2.Range("B" & f.Row)
      Range("F11") = sh2.Range("F" & f.Row)
      Range("F13") = sh2.Range("K2")
    Else
      MsgBox "name does not exist"
    End If
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[Gregm66]

Thankyou for your reply DanteAmor,

The code you supplied works perfectly.

Thankyou once again for your help, greatly appreciated..

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[JTS25]

Try this

Change "C:C" for the column with the names.
Put the code in sheet events.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Target.Address(0, 0) = "B9" Then
    Dim sh2 As Worksheet, f As Range
    Set sh2 = Sheets("Fees Paid")
    Set f = sh2.Range("[COLOR=#ff0000]C:C[/COLOR]").Find(Range("B9"), , xlValues, xlWhole)
    If Not f Is Nothing Then
      Range("B11") = sh2.Range("E" & f.Row)
      Range("B13") = sh2.Range("A" & f.Row)
      Range("F9") = sh2.Range("B" & f.Row)
      Range("F11") = sh2.Range("F" & f.Row)
      Range("F13") = sh2.Range("K2")
    Else
      MsgBox "name does not exist"
    End If
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Not to hijack this thread, but what would go into the section of code where you state "Change "C:C" for the column with the names"

Could you also explain what these two lines of code mean:

If Target.Count > 1 Then Exit Sub
If Target.Address(0, 0) = "B9" Then

Also, say A2 on down is what triggers matching values for B2,C2,D2 and the number of rows varies. Would the above code work if I tweaked it a little? Thanks for any info, I basically would like to try to add this onto some code I already have.

 

[DanteAmor]

I put my comments

Not to hijack this thread,
Do not worry

but what would go into the section of code where you state "Change "C:C" for the column with the names"
I do not understand what you mean.


Could you also explain what these two lines of code mean:

If Target.Count > 1 Then Exit Sub
[/COLOR][COLOR=#ff0000]If you modify more than one cell then it exits the program.[/COLOR][COLOR=#333333]

If Target.Address(0, 0) = "B9" Then

If the modified cell is cell B9 then the instructions are executed.

Also, say A2 on down is what triggers matching values for B2,C2,D2 and the number of rows varies. Would the above code work if I tweaked it a little? Thanks for any info, I basically would like to try to add this onto some code I already have.

I could gladly modify the code for your needs. But I am not sure what you require.


I recommend you create a new thread where you explain in detail how your data is, if you have more than one sheet, what data you have on each sheet, what data you have in each column. What data do you want to capture, in which cell and what do you expect?

 

[JTS25]

Thanks @DanteAmor

Here is a thread that I have already started, and at the bottom of it I explain what I'm looking to do:
https://www.mrexcel.com/forum/excel...-sheet2-into-sheet1-within-same-workbook.html