Hi, I have come somewhat stuck using the function .Find(what:=Fruit, LookIn:=xlValues, LookAt:=xlWhole).
What I am trying to do is to update my data in a manner shown in the example below. A description of what the data is doing:
If fruit in list update Total and Cost
If fruit not in list add in first empty row
Hope this makes sense.
Many thanks
SAMPLE DATA:
<tbody>
</tbody>
So using the example above my code so far looks like this( I have highlighted the problem area in red):
Sub Update_Test()
Application.DisplayAlerts = False
Application.EnableEvents = True
Dim Timestamp As Date
Timestamp = Now()
Dim LastRow As Integer
Dim LastRow2 As Long
Dim i As Integer
Dim erow As Integer
Dim Sheet As String
Sheet = "Summary"
Dim found As Range
Fruit = ActiveSheet.Range("A" & Rows.Count).Value
LastRow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If Cells(i, 1) = 1 Then
Range(Cells(i, 3), Cells(i, 5)).Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\JP\Desktop\VBA\Test.xls"
Worksheets(Sheet).Select
Do While Not Fruit = ""
'search through column A
Set found = Columns("A:A").Find(what:=Fruit, LookIn:=xlValues, LookAt:=xlWhole)
If found Is Nothing Then
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
End If
Loop
ActiveSheet.PasteSpecial
With ActiveSheet.Range("A11:C40")
ActiveSheet.Cells.End(xlUp).Offset(0, 8) = "as @ " & Now()
.Offset(0, 10).Cells.End(xlUp).Interior.ColorIndex = 28
End With
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End If
Next i
End Sub
What I am trying to do is to update my data in a manner shown in the example below. A description of what the data is doing:
If fruit in list update Total and Cost
If fruit not in list add in first empty row
Hope this makes sense.
Many thanks
SAMPLE DATA:
Existing Data: | ||
Fruit | Total | Cost |
Apple | 2 | £0.10 |
Orange | 1 | £0.05 |
Pear | 3 | £0.30 |
Incoming Data: | ||
Fruit | Total | Cost |
Apple | 1 | £0.05 |
Orange | 1 | £0.05 |
Pear | 1 | £0.10 |
Kiw1 | 2 | £0.20 |
Updated Data: | ||
Fruit | Total | Cost |
Apple | 3 | £0.15 |
Orange | 2 | £0.10 |
Pear | 4 | £0.40 |
Kiw1 | 2 | £0.20 |
<tbody>
</tbody>
So using the example above my code so far looks like this( I have highlighted the problem area in red):
Sub Update_Test()
Application.DisplayAlerts = False
Application.EnableEvents = True
Dim Timestamp As Date
Timestamp = Now()
Dim LastRow As Integer
Dim LastRow2 As Long
Dim i As Integer
Dim erow As Integer
Dim Sheet As String
Sheet = "Summary"
Dim found As Range
Fruit = ActiveSheet.Range("A" & Rows.Count).Value
LastRow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If Cells(i, 1) = 1 Then
Range(Cells(i, 3), Cells(i, 5)).Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\JP\Desktop\VBA\Test.xls"
Worksheets(Sheet).Select
Do While Not Fruit = ""
'search through column A
Set found = Columns("A:A").Find(what:=Fruit, LookIn:=xlValues, LookAt:=xlWhole)
If found Is Nothing Then
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
End If
Loop
ActiveSheet.PasteSpecial
With ActiveSheet.Range("A11:C40")
ActiveSheet.Cells.End(xlUp).Offset(0, 8) = "as @ " & Now()
.Offset(0, 10).Cells.End(xlUp).Interior.ColorIndex = 28
End With
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End If
Next i
End Sub