Repeated rows with formula based on filtered selection

[ryan7]

I'm not sure what the name of this thread should have been.

I have a document which has vlookups.
In cell A14; =IFERROR(VLOOKUP($G$7,'FULL LIST'!B2:L203,2,FALSE),"")
This formula runs down to cell A39.

When I change cell G7 to a different value, it is populates cells with repeated info.

I tried to put this VBA to remedy it but it does not work.

Private Sub Worksheet_Change(ByVal Target As Range)
' Check if the changed cell is G7
If Not Intersect(Target, Me.Range("G7")) Is Nothing Then
' Clear the target cells
Me.Range("A14, B14, D14, F14").ClearContents

' Re-apply VLOOKUP formulas to the target cells
Me.Range("A14").Formula = "=IFERROR(VLOOKUP($G$7,'FULL LIST'!B2:L203,2,FALSE), """")"
Me.Range("B14").Formula = "=IFERROR(VLOOKUP($G$7,'FULL LIST'!B2:L203,3,FALSE), """")"
Me.Range("D14").Formula = "=IFERROR(VLOOKUP($G$7,'FULL LIST'!B2:L203,8,FALSE), """")"
Me.Range("F14").Formula = "=IFERROR(VLOOKUP($G$7,'FULL LIST'!B2:L203,6,FALSE), """")"
End If
End Sub


any suggestions?

 

[ryan7]

Anyone willing to take a stab at this?

 

[My Aswer Is This]

Anyone willing to take a stab at this?

Why are you using Vba to enter formulas into ranges?

 

[ryan7]

Why are you using Vba to enter formulas into ranges?

Clearly, I'm stuck.

Repeating rows might occur due to residual data from previous selections. When i changed the selection in cell G7, the formulas populate the specified cells, but any previously filled cells are not cleared automatically.

To ensure that only the relevant cells are populated, I tried to clear the cells before updating them. This can be achieved with VBA to automate the clearing process when the selection changes.

Not working though.

 

[My Aswer Is This]

You said:

"
To ensure that only the relevant cells are populated, I tried to clear the cells before updating them. This can be achieved with VBA to automate the clearing process when the selection changes.

Not working though."

What's not working?

Saying "Not working though" is not specific.

 

[ryan7]

You said:

"
To ensure that only the relevant cells are populated, I tried to clear the cells before updating them. This can be achieved with VBA to automate the clearing process when the selection changes.

Not working though."

What's not working?

Saying "Not working though" is not specific.

As an, example, if I change cell G7 to customer A, it will return 4 rows of correct information via the vlookup.
If I change cell G7 to customer B, it's returning multiple rows of repeated information when it should only return 1 row of new info.
When I change G7 to any other value, it's repeating the one row the length of workbook.

Thanks

 

[My Aswer Is This]

I really do not think I can help you but I'm sure someone else here will be able to.