Please can you explain this code

[ipbr21054]

Morning,
I have an issue on one worksheet & trying to find the reason why.
Please could you explain what the supplied code actually does.
Please could you then advise another way to write it.

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range, d As Range
Set d = Intersect(Target, Range("G13:O17", "G27:O42"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each C In d
        If C.Column <> 14 Then
            If Not C.HasFormula Then C = UCase(C)
        End If
    Next
        If Intersect(Target, Range("G13")) Is Nothing Then Exit Sub
    Dim rName As Range, srcWS As Worksheet
    Set srcWS = Sheets("DATABASE")
    Set rName = srcWS.Range("A6:A" & srcWS.Cells(srcWS.Rows.Count, 1).End(xlUp).Row).Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not rName Is Nothing Then
        Range("N15") = srcWS.Range("B" & rName.Row)
        Range("N14") = srcWS.Range("D" & rName.Row)
        Range("N16") = srcWS.Range("L" & rName.Row)
        Range("N17") = srcWS.Range("W" & rName.Row)
        Range("G14") = srcWS.Range("R" & rName.Row)
        Range("G15") = srcWS.Range("S" & rName.Row)
        Range("G16") = srcWS.Range("T" & rName.Row)
        Range("G17") = srcWS.Range("U" & rName.Row)
        Range("G18") = srcWS.Range("V" & rName.Row)
        Application.EnableEvents = True
        End If
End Sub

 

[ipbr21054]

Hi,
Ive now used the code when you mentioned lazy part and all done.

On the DATABASE a cell is empty so when i select the customer name on the INV sheet drop down list that respective cell then shows 0
Can we as opposed to seeing 0 just have a blank cell

Also INV sheet returns number 6 in cell H13 for some reason

Thanks

 

[baitmaster]

So you have the customer name but no value associated. Therefore the INDEX formula is returning 0 for the empty cell. There's several ways to do this, depending what you want

Easiest, is simply to change the number format. Say you want all numbers to have 2dp, a number format for this is 0.00;-0.00 which means 0.00 for positive numbers (before the semi-colon) and -0.00 for negative numbers. But you can extend this to 0.00;-0.00; and leave the section after the second semi-colon blank, which tells Excel to not display the zero. The zero-value is still there, you just can't see it. Since this makes it hard to spot there is a formula, my preferred format is 0.00;-0.00;"-" which shows zeroes as dashes. It's a clean look but still informative

Alternative approach is to add a further IF statement to ask if the result is zero, and replace with "" instead

 

[ipbr21054]

Hi,
With all the ocdes now inserted into the cells as advised this is what i then do for making an invoice.
Cell 13 using the drop down i select say ROD JONES.
Then i see vales entered in the following cells.
G14 G15 G17 G18 These values are all correct.
G16 shows 0 because on the DATABASE sheet at T the cell is empty, if some value was in that cell then G16 would show that value as opposed to the 0

So as you suggest some kind of code that if no value is in the DATABASE cell then its cell on the INV sheet should be empty and not 0

...............................

The second item i mentioned is the following.
After making a selection from the drop down list and all my cells have there values entered is then see in cell H13 on the INV sheet the number 6
This is the same cell where i had put the code =IF(ISERROR(MATCH(G13,DATABASE!A:A,0)),"",MATCH(G13,DATABASE!A:A,0))

This otherwise works much better than what was there before.
Also once done i will start a new post as something i have in place creates an issue with this new code,but like i say i will start a fresh post for that.

Thanks if you can advise what i need to now insert for the 0 and 6 issue