Please advise on this code & maybe another alternative

[ipbr21054]

On my sheet i have a database where when i make a selection from a drop down the cells are filled with values from a range & also an IF vlookup is used.
All works well until i wish to add to the database & code needs to be updated.

Code in use in cell C3

=IFERROR(IF(VLOOKUP($B18,$F:$H,3,FALSE)=0,"", VLOOKUP($B18,$F:$H,3,FALSE)),"")

Now when i try to alter the code & paste it in my first cell i notice that when i leave the cell its removed thus cell being empty.

I code in use is shown below & ive tracked the issue down to the line in Red.
Who can it be written another way without my issue.
Thanks

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.CountLarge > 1 Then Exit Sub

If Intersect(Target, Range("A2:D29")) Is Nothing Then Exit Sub

Application.EnableEvents = False

Target = UCase(Target)

Application.EnableEvents = True

End Sub

Is this code just for changing the text to uppercase OR is it doing something else ?

 

[Alex Blakenburg]

The default property of a range is Value, so
Target =Target is essentially the same as
Target.Value = Target.Value
This will replace the formula with the "value" the formula returns.
If the formula returns "" as will happen if the vlookup return value is 0 or if the IFERROR kicks in then the expression will convert the "" to an empty cell.
The UCase just uppercases any text value that can be upper cased but the formula will be gone.

 

[ipbr21054]

So what should i do.
I need to update as & when i need too.
I have OCD so Uppercase is a must for me.

Removing Target = UCase (Target) allowed me to update but when its in place the Formula is removed from cell.

 

[Alex Blakenburg]

Change your formula to

=IFERROR(IF(VLOOKUP($B18,$F:$H,3,FALSE)=0,"", UPPER(VLOOKUP($B18,$F:$H,3,FALSE))),"")

 

[ipbr21054]

OK
Do i also delete that Worksheet_Change code or just the UCase part of it

I see that applies UCase only to the column its in whereas before its applied to my range on the sheet

 

[MARK858]

Adjust the range in the code so it doesn't include cells with the formula in them

 

[ipbr21054]

Ialso have another formula in the next column of which is

=IFERROR(VLOOKUP($B3,$F:$H,2,FALSE),"")

Please advise how it would be written to also take onboard the UPEER part

Thanks

Looking to edit range now

 

[MARK858]

=IFERROR(UPPER(VLOOKUP($B3,$F:$H,2,FALSE)),"")

 

[ipbr21054]

Thanks I missed out the ( between UPPER & VLOOKUP