Two Ifs no else (1 Viewer)

[mdjks]

The code below works but only the first one. If I change the order and step through it will give me the user name. I have been searching the web but I'm not using the right search criteria. Help would be greatly appreciated.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim newRange As Range
Set newRange = ActiveCell.Offset(0, -1)

Dim uName As String
uName = Environ("username")

Dim dTeRange As Range
Set dTeRange = ActiveCell.Offset(0, 1)

Dim dTe As Date
dTe = Date


If newRange.Column = 6 Then
    dTeRange = dTe
If newRange.Column = 6 Then
    newRange.Value = uName

End If
End If


End Sub

 

[pbaldy]

The test is the same, so:

If newRange.Column = 6 Then
    dTeRange = dTe
    newRange.Value = uName
End If

 

[MarkK]

Also, you don't always have to declare a variable and assign it a value. Here's refactored code the does exactly the same thing as what you have . . .

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    With ActiveCell.Offset(0, -1)
        If .column = 6 Then
            ActiveCell.Offset(0, 1) = Date
            .Value = Environ("username")
        End If
    End With
End Sub

. . . but it just uses the direct references, rather than declaring and assigning them to variables. Just as an option, it's not right or wrong.

 

[mdjks]

Thank you, I tried this method but it does the same thing, populates the first then jumps right back to the top.

Pbaldy, that was how I wrote it originally but it jumps back up.

I'm just sort of at a loss. Tried this at work using 2013 and at home with 2016

 

[MarkK]

I don't know Excel very well, but do you need to assign the date to a property of the range, like . . .

            ActiveCell.Offset(0, 1).Text = Date

Also, have you shown us all the code, or do you have an error handler running that you have omitted for clarity?
Cheers,

 

[mdjks]

there is no other code and both statements work, depending on which one is listed first.