Updating data Using: .Find(what:=Fruit, LookIn:=xlValues, LookAt:=xlWhole)

Kel09

New Member
Joined
Jul 28, 2014
Messages
23
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:

Existing Data:
FruitTotalCost
Apple2£0.10
Orange1£0.05
Pear3£0.30
Incoming Data:
FruitTotalCost
Apple1£0.05
Orange1£0.05
Pear1£0.10
Kiw12£0.20
Updated Data:
FruitTotalCost
Apple3£0.15
Orange2£0.10
Pear4£0.40
Kiw12£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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Do not enter numbers as string(text-with a quotation in front). do not add the currency icon.
just type 0.05,0.20 etc. then format it as currency.

you do not need a macro for this


if you make entries in the first two sheets called "existing" and "addition"
then the third sheet "updated" will have the following formula
the formulas in the row no. 2 in "updated" is copied down


updated

*ABC
1FruitTotalCost
2Apple30.15
3Orange20.1
4Pear40.4
5Kiw120.2

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
A2=addition!A2
B2=existing!B2+addition!B2
C2=existing!C2+addition!C2

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top