VBA to use OnChange event to vLookup cell value in a table to complete columns B,C, D if value in column is matched.

[SyedGangsta]

I have a lot of appreciation for all the experts on here, so I was wondering if any one would be able to come up with a solution for the question/problem below:

Sheet 'TABLE' has a range named LUTable which is populated with data. I have also applied a range name for each column:
Col A= Manufacturer
Col B= Model
Col C = Shape
Col D = Price


[table="width: 200, class: grid"]
[tr]
[td]Make [/td]
[td]Name[/td]
[td]Type[/td]
[td] Cost[/td]
[/tr]
[tr]
[td]Toyota[/td]
[td]RAV4 [/td]
[td] SUV[/td]
[td]£20,000[/td]
[/tr]

[tr]
[td] Ford[/td]
[td] Kuga [/td]
[td]SUV[/td]
[td]£22,000[/td]
[/tr]

[tr]
[td]Kia[/td]
[td]i30[/td]
[td]Saloon[/td]
[td]£18,000[/td]
[/tr]

[tr]
[td]BMW[/td]
[td]330ci[/td]
[td]Coupe[/td]
[td]24,000[/td]
[/tr]

[tr]
[td]Skoda[/td]
[td]Superb[/td]
[td]Hatch[/td]
[td]14,000[/td]
[/tr]

[/table]


There is another Sheet called data 'DATA' where I would like the user to fill a similar table with same headings as one in sheet called 'TABLE'.

The idea is if the user enters a value in the Make column that matches on the LUTable then columns B,C and D will auto complete, if a match is not made then the user continues to fill the details in column B,C and D.

To do this I am using a Worksheet_Change event that monitors column A on the 'DATA' sheet, I have named this range Make.

I know its possible to do this using excel formulas but then the option for the user to insert their own value is taken away, the VBA approach is more of a predictive filling in, the user is still free to change what ever cell values are completed by VBA.

the equivalent version of the excel formula that I have working is:

=IF(COUNTIF(Manufacturer,A13)=1,VLOOKUP(A13,LUTable,2,FALSE),"")


The code that I am using in VBA is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sMake As String
Dim sCar As String
Dim sType As String
Dim nCost As Integer
Dim sheet As Worksheet

If Not Intersect(Target, Range("Make")) Is Nothing Then

sMake = Target.Value

If Target.Value = "Ford" Then
Target.Offset(0, 1).Value = "Kuga"
Target.Offset(0, 2).Value = "SUV"
Target.Offset(0, 3).Value = "22,000"

Else:
Target.Offset(0, 1).Value = ""
Target.Offset(0, 2).Value = ""
Target.Offset(0, 3).Value = ""
End If
End If
End Sub

The purple coloured code is just test Target.offset and this idea, I need to somehow incorporate the excel If and CountIf formulas to check if the user value entered in range 'Make' is already on the LUTable, if it is then values in columns B,C,D can be pulled using VLookup or something similar using VBA or some thing similar.

Your help much appreciated.

 

[SyedGangsta]

I have been able to make some progress on this myself, below works as required.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sMake As String
Dim nMake As Integer
Dim sName As String
Dim sType As String
Dim nCost As Single
Dim rTableLU As Range ' the look up table
Dim rManufacturer As Range ' The Make column on Lookup Table

If Not Intersect(Target, Range("Make")) Is Nothing Then

sMake = Target.Value
Set rTableLU = Sheets("TABLE").Range("LUTable")
Set rManufacturer = Sheets("TABLE").Range("Manufacturer")

nMake = Application.WorksheetFunction.CountIf(rManufacturer, sMake) 'Checks if the value entered in the Make entered is found on Clumn A of LUTable (Manfacturer)

If nMake = 1 Then

sName = Application.WorksheetFunction.VLookup(sMake, rTableLU, 2, False)
sType = Application.WorksheetFunction.VLookup(sMake, rTableLU, 3, False)
nCost = Application.WorksheetFunction.VLookup(sMake, rTableLU, 4, False)

Target.Offset(0, 1).Value = sName
Target.Offset(0, 2).Value = sType
Target.Offset(0, 3).Value = nCost

Else

Target.Offset(0, 1).Value = ""
Target.Offset(0, 2).Value = ""
Target.Offset(0, 3).Value = ""

End If

End If
End Sub


The issue I have now that some one may be able to help with is that if I clear more than one cell at once from the Make column, I get vba errorr message which is annoying.

I think the worksheet_change event does not like it if more than one cell changes, but how can I over come this?

I am also working putting in a sum formula as part of this autocomplete columns using VBA, so still need some assistance of possible.