Adding an if formula to existing VBA code

[Olympus64]

Hi!!! I am lost on trying to add an "if" statement to an existing VBA code. I would =IF(OR(G3="",H3=""),"-",IF(G3=H3,"Ship","Do Not Ship")) to
the VBA code below
Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
For x = 2 To 100
If Cells(x, 1).Value <> "" And Cells(x, 2).Value = "" Then
Cells(x, 2).Value = Date & " " & Time

Cells(x, 2).NumberFormat = "m/d/yyyy h:mm AM/PM"
End If
Next
Range("B:B").EntireColumn.AutoFit

End Sub

 

[Zot]

I have no idea where you want to write Ship, Do Not Ship, or - on the worksheet. You need to replace xx with cell address.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim x As Integer
For x = 2 To 100
    If Cells(x, 1).Value <> "" And Cells(x, 2).Value = "" Then
        Cells(x, 2).Value = Date & " " & Time
        Cells(x, 2).NumberFormat = "m/d/yyyy h:mm AM/PM"
    End If
Next

If Range("G3") = "" Or Range("H3") = "" Then
    Range("xx") = "-"
ElseIf Range("G3") = Range("H3") Then
    Range("xx") = "Ship"
Else
    Range("xx") = "Do Not Ship"
End If

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

End Sub

 

[Olympus64]

I have no idea where you want to write Ship, Do Not Ship, or - on the worksheet. You need to replace xx with cell address.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim x As Integer
For x = 2 To 100
    If Cells(x, 1).Value <> "" And Cells(x, 2).Value = "" Then
        Cells(x, 2).Value = Date & " " & Time
        Cells(x, 2).NumberFormat = "m/d/yyyy h:mm AM/PM"
    End If
Next

If Range("G3") = "" Or Range("H3") = "" Then
    Range("xx") = "-"
ElseIf Range("G3") = Range("H3") Then
    Range("xx") = "Ship"
Else
    Range("xx") = "Do Not Ship"
End If

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

End Sub

Thank you Zot for your help. This was really helpful.

 

[offthelip]

When writing to the worksheet using the Worksheet change event, you will get into a unending loop because the writing to the worksheet triggers the worksheet change event again, which writes to the worksheet which trigger the event again, forever or until you abort. To avoid this you MUST turn off events before you write to the worksheet, and then turn them on again otherwise EXCEL won't work properly like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
Application.EnableEvents = False  ' YOU MUST ADD THIS LINE Turn off events

For x = 2 To 100
If Cells(x, 1).Value <> "" And Cells(x, 2).Value = "" Then
Cells(x, 2).Value = Date & " " & Time

Cells(x, 2).NumberFormat = "m/d/yyyy h:mm AM/PM"
End If
Next
Range("B:B").EntireColumn.AutoFit
Application.EnableEvents = True    ' YOU MUST ADD THIS LINE Turn events back on again
 
End Sub

Note Zot as well

 

[Zot]

When writing to the worksheet using the Worksheet change event, you will get into a unending loop because the writing to the worksheet triggers the worksheet change event again, which writes to the worksheet which trigger the event again, forever or until you abort. To avoid this you MUST turn off events before you write to the worksheet, and then turn them on again otherwise EXCEL won't work properly like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
Application.EnableEvents = False  ' YOU MUST ADD THIS LINE Turn off events

For x = 2 To 100
If Cells(x, 1).Value <> "" And Cells(x, 2).Value = "" Then
Cells(x, 2).Value = Date & " " & Time

Cells(x, 2).NumberFormat = "m/d/yyyy h:mm AM/PM"
End If
Next
Range("B:B").EntireColumn.AutoFit
Application.EnableEvents = True    ' YOU MUST ADD THIS LINE Turn events back on again
 
End Sub

Note Zot as well

That is how you learn VBA. There are many situation that event trigger can be more mind boggling especially when you have many other events in your program.

 

[Phuoc]

Change:

Cells(x, 2).Value = Date & " " & Time

to:

Cells(x, 2).Value = Now

 

[Olympus64]

Change:

Cells(x, 2).Value = Date & " " & Time

to:

Cells(x, 2).Value = Now

Hi Zot and Phuoc I added a snapshot of what I am trying to achieve with your help.

I want to be able to scan two barcodes (Col A and Col B) capturing the instructions (Col C) to "ship" if the barcodes match, and "Do not ship" if the barcodes do not match. I need the date and time stamp (Col D) after the condition has been met in Col C.

VBA Code
Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
For x = 2 To 100
Dim xx As Integer
For xx = Range("c:c").Select
If Cells(x, 1).Value = Cells(x, 2).Value = "" Then
Cells(x, 4).Value = Date & " " & Time
Cells(x, 4).NumberFormat = "m/d/yyyy h:mm AM/PM"
End If
Next

If Range("A2") = "" Or Range("B2") = "" Then
Range("xx") = "-"
ElseIf Range("A2") = Range("B2") Then
Range("xx") = "Ship"
Else
Range("xx") = "Do Not Ship"
End If

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

End Sub

 

[Zot]

Any reason why you need to put your macro under Worksheet_Change event?

 

[Olympus64]

Any reason why you need to put your macro under Worksheet_Change event?

I was watching a video and thats the way the instructor explained how to do it.

 

[Olympus64]

I was watching a video and thats the way the instructor explained how to do it.

It all started with scanning a barcode and getting the date and time. I then miserably failed at trying to build on it.