markkeith
09-05-2020, 08:31 PM
I'm trying to populate a table with data from another worksheet based on result on Vlookup.
Sheet1 Range("receiptNum") has a number data that will be searched from Sheet4("transcTable") and when I run the Sub it actually found the "matched number" however it continue to write data all the way down to destination table. Also what code do I need to add to handle error if No data found?
Here's my code:
Sub recordSearch()
Dim ws As Worksheet
Dim intItems As Integer
Dim cellx As Range, rowX As Range
Set rowX = Sheet1.Range("A12")
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect
Next ws
Range("date").Value = Application.VLookup(Range("receiptNum"), Range("transcTable"), 2, False) 'Date
Range("name").Value = Application.VLookup(Range("receiptNum"), Range("transcTable"), 3, False) 'Name
intItems = 0
For Each cellx In Range("receiptNumRec")
CellXRow = CellXRow + 1
If Range("receiptNum").Value > "" Then
intItems = intItems + 1
rowX.Offset(intItems - 1, 1).Value = Application.VLookup(Range("receiptNum"), Range("transcTable"), 1, False)
If rowX.Offset(intItems - 1, 1).Value > "" Then
rowX.Offset(intItems - 1).Value = intItems 'Item Num
End If
End If
Next cellx
For Each ws In ActiveWorkbook.Worksheets
ws.Protect , DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True
Next ws
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Sheet1 Range("receiptNum") has a number data that will be searched from Sheet4("transcTable") and when I run the Sub it actually found the "matched number" however it continue to write data all the way down to destination table. Also what code do I need to add to handle error if No data found?
Here's my code:
Sub recordSearch()
Dim ws As Worksheet
Dim intItems As Integer
Dim cellx As Range, rowX As Range
Set rowX = Sheet1.Range("A12")
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect
Next ws
Range("date").Value = Application.VLookup(Range("receiptNum"), Range("transcTable"), 2, False) 'Date
Range("name").Value = Application.VLookup(Range("receiptNum"), Range("transcTable"), 3, False) 'Name
intItems = 0
For Each cellx In Range("receiptNumRec")
CellXRow = CellXRow + 1
If Range("receiptNum").Value > "" Then
intItems = intItems + 1
rowX.Offset(intItems - 1, 1).Value = Application.VLookup(Range("receiptNum"), Range("transcTable"), 1, False)
If rowX.Offset(intItems - 1, 1).Value > "" Then
rowX.Offset(intItems - 1).Value = intItems 'Item Num
End If
End If
Next cellx
For Each ws In ActiveWorkbook.Worksheets
ws.Protect , DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True
Next ws
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub