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

 

[gallen]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range, d As Range


'Check if the target cell is within G13:017 or G27:042. If not exit sub
Set d = Intersect(Target, Range("G13:O17", "G27:O42"))
If d Is Nothing Then Exit Sub
'prevent events from triggering
Application.EnableEvents = False
    
    'loop through every cell in range 'd'
    For Each C In d
        'if the cell isnlt in column 'N'
        If C.Column <> 14 Then
            'if the cell doesn't have a formula then force to uppercase
            If Not C.HasFormula Then C = UCase(C)
        End If
    Next
    
    'if the target isn't G13 then exit sub'
    If Intersect(Target, Range("G13")) Is Nothing Then Exit Sub
    
    'If we get here we must be working with G13
    Dim rName As Range, srcWS As Worksheet
    
    'Set the sheet variable
    Set srcWS = Sheets("DATABASE")
    
    'Find the value in G13 (Target) in A6 down to last row in column A
    Set rName = srcWS.Range("A6:A" & srcWS.Cells(srcWS.Rows.Count, 1).End(xlUp).Row).Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    
    'if rname isn't nothing then the value in G13(Target) was found
    If Not rName Is Nothing Then
        'set the value of N15 on the worksheet this code is in to the same value as _
        column "B" on the same row the value was found on Sheets("DATABASE")
        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)
        're-enable events
        Application.EnableEvents = True
        End If
End Sub

 

[baitmaster]

Option Explicit


' the following code runs whenever the worksheet is changed - not just recalculated, or the selection is moved, but some data or formula is actually changed
' Target is the cell or cells that are changed


Private Sub Worksheet_Change(ByVal Target As Range)


' create two range objects
Dim C As Range, d As Range


' make the range d be the overlap of Target and the range shown. If there is no overlap, "Nothing" is returned and the code ends
Set d = Intersect(Target, Range("G13:O17", "G27:O42"))
If d Is Nothing Then Exit Sub


' the next line stops automatic events running until further notice. This is usually to stop this code from accidentally running itself - running this code would change the worksheet, which would automatically run the code...
Application.EnableEvents = False
    ' look at every cell with the range of overlapping cells
    For Each C In d
        ' check if the cell is not in column N
        If C.Column <> 14 Then
            ' if it is a Constant (i.e. not a formula) then convert it to uppercase
            If Not C.HasFormula Then C = UCase(C)
        End If
    Next
    
    ' now test to see if cell G13 was one of the cells originally changed. If not, then the code ends
    If Intersect(Target, Range("G13")) Is Nothing Then Exit Sub
    
    ' declare a raneg and worksheet object
    Dim rName As Range, srcWS As Worksheet
    
    ' Look on the worksheet named DATABASE
    Set srcWS = Sheets("DATABASE")
    
    ' look in column A, from rows 6 to the last used cell. Search this range to find the value of the original Target cell
    ' > this implies Target should only be one cell, not multiple cells, and so perhaps a test at the start should check for this
    Set rName = srcWS.Range("A6:A" & srcWS.Cells(srcWS.Rows.Count, 1).End(xlUp).Row).Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    
    ' If the value in Target was found in Column A in the previous row, then a number of cells are populated with data from the row on which that value was found
    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)
        
        ' switch events back on, so future changes will automatically run the code again. However, this line is in the wrong place. It will only run if the value was found
        ' Therefore if the value was not found, or cell G13 was not one of the cells originally changed, events will never be switched back on
        Application.EnableEvents = True
        End If
End Sub

 

[ipbr21054]

Hi,
Let me advise whats happens.

Looking at the above code i now have in place a different code which converts small case to upper case in a specific range.

Private Sub Worksheet_Change(ByVal Target As Range)If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

    On Error Resume Next

    If Not Intersect(Target, Range("G13:O51")) Is Nothing Then

        Application.EnableEvents = False

        Target = UCase(Target)

        Application.EnableEvents = True

    End If

    On Error GoTo 0

End Sub

Now the part that i think that is causing all my problems is the last part of the code above & needs to be added to the UCASE code just mentioned

Here is some information which will assist you.
I believe having it written another way will solve my issues.

I have a workbook with several sheets.
The sheet in question is called INV
I have a drop down list in cell G13
Data validation for cell G13 reads as source =INDIRECT("CUSTOMERSNAMEINV")
Table Manager reads Refers to as =Table23[CUSTOMERS NAME]
The information that worksheet INV requires is taken from worksheet called DATABASE


Here is what i would like or should happen.
Select a customers name from the drop down list on worksheet INV
Wth specific cell locations collect the value from the row in worksheet DATABASE & then put in specific cell on the worksheet INV


So Drop down list on worksheet INV at cell G13
Select A customer name from list.
The code should the get the required information from the rows on worksheet DATABASE

INV cell G14 requires value from DATABASE row R
INV cell G15 requires value from DATABASE row S
INV cell G16 requires value from DATABASE row T
INV cell G17 requires value from DATABASE row U
INV cell G18 requires value from DATABASE row V
INV cell N14 requires value from DATABASE row D
INV cell N15 requires value from DATABASE row B
INV cell N16 requires value from DATABASE row L
INV cell N17 requires value from DATABASE row W

 

[baitmaster]

Is there a reason you aren't using formulas to do this? A basic VLOOKUP would do this, or preferably a combination of INDEX and MATCH

 

[ipbr21054]

Only the simple answer that im not sure what to do to be honest.
I was helped with the code mentioned but now must do something as it doesnt work correct and causes issue on the page.
can you advise maybe.

Thanks

I lived in Bristol for 43 years now down by Thatchers Cider in Banwell...
I see the bikes go past each Thursday for the bike meet on the sea front

 

[baitmaster]

Lol, small world eh?

I'm using two formulas, MATCH tells me which row your data is on, and INDEX returns a value from other columns on the same row. Later we'll need to add a check in case the data doesn't exist else you'll get an #N/A error

In cell H13 I've got the row number by adding the formula =MATCH(G13,Database!A:A,0)
Then in G14 =INDEX(Database!R:R,$H$13) gets the value you want. Repeat this formula in other cells, changing column R as necessary

I prefer VBA coding to normal spreadsheet work, but only when it's necessary. I don't know why you've been advised to go down the coding route unless I'm missing something here, since formulas are a much simpler approach in this instance

Re: handling the #N/A error, which version of Excel are you using? I'd add an IFNA formula into H13 but this only works in recent versions of Excel. The result would be =IFNA(MATCH(G13,Database!A:A,0),"") Alternatively you could use IFERROR in the same way =IFERROR(MATCH(G13,Database!A:A,0),"") or IF(ISERROR....) if you're using pre-2013 version of Excel

The second formula would need a simple IF statement added in =IF($H$13="","",INDEX(Database!R:R,$H$13))

 

[ipbr21054]

I am using 2007 i believe.
At present ive just put the codes mentioned below in the cells as advised.

[B]=MATCH(G13,Database!A:A,0)[/B]

[B]=INDEX(Database!R:R,$H$13)[/B]

I have a cell which does not show me the #N/A like the others but it shows me =INDEX(Database!W:W,$H$13)

Ive tried a few times now but each time the same

Also when i put IF(ISERROR(MATCH(G13,Database!A:A,0),"") into cell H13 it also shows what ive entered all across the page

 

[baitmaster]

So when you type the formula and hit ENTER it still just shows the formula on screen? This sounds like your cell format is set to Text instead of General / a number format. Change the number format of the cell and re-enter the formula, should fix this

I don't think IFERROR works with Excel 2007 either, so it seems IF(ISERROR(... is the correct approach, but you've written the formula wrong because I was slightly lazy in my explanation. Try =IF(ISERROR(MATCH(G13,DATABASE!A:A,0)),"",MATCH(G13,DATABASE!A:A,0))

 

[ipbr21054]

Update.
N18 now accepted code as cell was formatted as text as it requires phone number