Compile error: ambiguous name detected vba excel Jan-06-17

Question

when i add another VBA into the same Sheet its produce a Complile error: ambigous name detected vba 

please help on how can i accomplish this!

Answer 1

Hi

You can't have two event macro of the same type.

Worksheet_SelectionChange(ByVal Target As Range)

 so combine the two together.

Answer 2

Delete the line that is highlighted in blue, the line one above that:

End Sub

and the line below it:

Dim i As Integer

Then all should be good.

Answer 3

Did you not see or not like the answers you got yesterday to same question?

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-msoffice_custom/compile-error-ambiguous-name-detected-vba-excel/99948f47-dbf9-4fb2-9bc7-21908ca106da

Gord

Answer 4

Hi Bernie, tried and it didnt it work, this is what i have

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 4 Then

    Beep

    Cells(Target.Row + 1, 2).Activate

End If

    If Target.Column = 17 Then

    Beep

    Cells(Target.Row + 1, 15).Activate

End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer

For i = 2 To 100

If Cells(i, "B").Value <> "" And Cells(i, "C").Value <> "" And Cells(i, "D").Value <> "" And Cells(i, "F").Value = "" Then

Cells(i, "F").Value = Date & " " & Time

Cells(i, "F").NumberFormat = "m/d/yyyy h:mm AM/PM"

End If

Next

Range("F:F").EntireColumn.AutoFit

End Sub

End If

Dim i As Integer

For i = 2 To 100

If Cells(i, "N").Value <> "" And Cells(i, "O").Value <> "" And Cells(i, "P").Value <> "" And Cells(i, "R").Value = "" Then

Cells(i, "R").Value = Date & " " & Time

Cells(i, "R").NumberFormat = "m/d/yyyy h:mm AM/PM"

End If

Next

Range("R:R").EntireColumn.AutoFit

End Sub

can you please tried on your end to see what am i doing wrong?

thank you.

Answer 5

This will work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 4 Then

    Beep

    Cells(Target.Row + 1, 2).Activate

End If

    If Target.Column = 17 Then

    Beep

    Cells(Target.Row + 1, 15).Activate

End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer

For i = 2 To 100

If Cells(i, "B").Value <> "" And Cells(i, "C").Value <> "" And Cells(i, "D").Value <> "" And Cells(i, "F").Value = "" Then

Cells(i, "F").Value = Date & " " & Time

Cells(i, "F").NumberFormat = "m/d/yyyy h:mm AM/PM"

End If

Next

Range("F:F").EntireColumn.AutoFit

For i = 2 To 100

If Cells(i, "N").Value <> "" And Cells(i, "O").Value <> "" And Cells(i, "P").Value <> "" And Cells(i, "R").Value = "" Then

Cells(i, "R").Value = Date & " " & Time

Cells(i, "R").NumberFormat = "m/d/yyyy h:mm AM/PM"

End If

Next

Range("R:R").EntireColumn.AutoFit

End Sub

But your code checks 1600 cells anytime anything is changed (100 rows and 8 columns - twice - because the code is restarted by itself when a cell is changed by the code), so you might want to change the second sub to this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim i As Long

    If Intersect(Target, Range("B:D,N:P")) Is Nothing Then Exit Sub

    Application.EnableEvents = False

    For i = Target.Cells(1).Row To Target.Cells(Target.Cells.Count).Row

        If Cells(i, "B").Value <> "" And Cells(i, "C").Value <> "" And Cells(i, "D").Value <> "" And Cells(i, "F").Value = "" Then

            Cells(i, "F").NumberFormat = "m/d/yyyy h:mm AM/PM"

            Cells(i, "F").Value = Now

        End If

        If Cells(i, "N").Value <> "" And Cells(i, "O").Value <> "" And Cells(i, "P").Value <> "" And Cells(i, "R").Value = "" Then

            Cells(i, "R").NumberFormat = "m/d/yyyy h:mm AM/PM"

            Cells(i, "R").Value = Now

        End If

    Next i

    Application.EnableEvents = True

    Range("F:F,R:R").EntireColumn.AutoFit

End Sub