VBA code not working to automatically move the cursor in column B after data entry in column E.

[pmoghe]

Hi, I am working on building a tool where we enter the value in cell B, values populate in columns C, D and E based off V-look up. After entering the value in Col. B, the cursor goes automatically to col H. After data entry (barcode scan) in col H, the cursor should come back to next row in Col B. And should keep on following the loop.

The code that I have works for the first row, but for the repetition on the next row it fails and shows an error message to end or debug. Can someone please help with a fix?

Objective is that similar to row 1, it should follow the same flow in the next row (Data entry in col B, auto populate in col C,D,E, cursor direct to col H and scan entry in Col H to take it to the next row col B again.

Thanks in advance!

 

[Joe4]

Welcome to the Board!

Please post the VBA code you are using.
Please see here for directions on posting your VBA code: How to Post Your VBA Code

 

[pmoghe]

Hi- Please see the code script below.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim sht As Worksheet
Dim KeyC1 As Range
Dim KeyC8 As Range
Dim lsc As String

Set sht = ActiveSheet

'dpt = Range("B1").Value 'notw
'typ = Range("C1").Value

'If dpt = "" Or typ = "" Then
'MsgBox "You need to select the Department and/or Scan Sheet Type before using.", vbOKOnly, "ERROR"
'Exit Sub
'End If
'If Target.Cells.Count > 1 Then Exit Sub 'note

If Not Intersect(Target, Range("B3:B600")) Is Nothing Then
With Target(1, 3)
.Value = Now
End With
If Range("C1").Value = "Labor Share" Then
With Target(1, 7) 'Note 7
.Select
End With
End If
ActiveWorkbook.Save
End If

'Cells(Rows.Count, 1).End(xlUp).Row

'Set KeyC1 = Range("B3:B202")

'If Not Application.Intersect(KeyC1, Range(Target.Address)) Is Nothing Then
' Range("B3").Select
' lRow = Cells(Rows.Count, 2).End(xlUp).Row
' Range("I" & lRow).Select
'End If

rowNum = ActiveCell.Row
bid = ""
If rowNum > 1 Then
lsc = Range("H" & rowNum - 1).Value 'note -1
bid = Range("B" & rowNum).Value
cCol = ActiveCell.Column
Else:
Exit Sub
End If

If Range("C1").Value = VTO Or Range("C1").Value = "PRE-VTO" Then
Range("B" & rowNum).Select
ElseIf cCol = 8 And bid = "" Then
Range("B" & rowNum).Select
End If

'ActiveWorkbook.Save

'Set KeyC8 = Range("I3:I202")

'If Not Application.Intersect(KeyC8, Range(Target.Address)) Is Nothing Then
' Range("B" & rowNum).Select
'lRow = Cells(Rows.Count, 2).End(xlUp).Row
'End If


'If cCol = 8 Then
' Exit Sub
'If Range("C1").Value = "Labor Share" And lsc = "" And bid <> "" Then
' Range("H" & rowNum - 1).Select
'Else: Range("B" & rowNum).Select
'End If


If lRow = 1 Or lRow = 2 Or cCol = 8 Then
Exit Sub
Else:
bc1 = Range("B" & rowNum - 1).Value
bc2 = Range("B" & rowNum - 2).Value
gc1 = Range("H" & rowNum - 1).Value 'note -1
End If

If bc1 = "" Then
Exit Sub
ElseIf bc1 = bc2 Then
Range("C" & rowNum - 1).ClearContents
Range("B" & rowNum - 1).ClearContents
Range("B" & rowNum - 1).Select
ElseIf bc1 = gc1 Then
Range("H" & rowNum - 1).ClearContents 'note -1
Range("B" & rowNum - 1).Select
End If

End Sub

Sub jumpnext()
Range("B" & ActiveCell.Row + 1).Select

ActiveWorkbook.Save

End Sub

 

[Joe4]

Where does your data actually begin?
You seem to be looking in "B3:B600", which presumes it starts in row 3, but then you also hard-coded references to row 1, i.e.
Range("C1")

Can you also post a small sample of your code?
Maybe that will help clarify things.

Also note that your code will call itself. A "Worksheet_Change" event procedure is fired whenever a value is entered on your sheet. But your code itself is entering data in cells on the sheet! So the code will fire itself! If not careful, you can get caught in an infinite loop!

Typically, when updating cells inside a "Worksheet_Change" event procedure, you first temporarily disable the code so it will not call itself, i.e.

Application.EnableEvents = False
'update to cell
Application.EnableEvents = True

 

[pmoghe]

Yes, B1 and B2 are table headers- and the actual data starts from B3. Can you please explain what do you mean by the sample of the code? I can provide the required information then.

The code is executing for the first row of the table i.e. row 3, but when it starts with row 4, the code crashes. The code works for any other row. The problem is it gets crashed on the immediate next row of whatever row I play with. The immediate next row crashes. I tried with alternate rows and it works.

 

[Joe4]

Yes, B1 and B2 are table headers- and the actual data starts from B3. Can you please explain what do you mean by the sample of the code? I can provide the required information then.

Oh geez, that was a typo. I meant to say a sample of your data, not code.
It would be helpful if we could see what the data actually looks like.