VBA: Updating and Overwriting Cell Contents

[NeoSez]

I want to create a macro to update a simple delivery DATA spreadsheet, based on the ORDER number. Not sure how to do this so that it replaces the current cell contents based on the Order # in the DATA sheet Column "I".

Criteria is listed below.

"UPDATES" sheet Column F --> "DATA" sheet Column A (Any new data based on these 2 columns data should be adding entire row to DATA after last used row)​

​

EXTRACT Date ONLY (MM/DD/YYYY) from "UPDATES" sheet Column T-->"DATA" sheet Column G BUT only if the word "DELIVERED" appears in Col. "T".​

​

"UPDATES" sheet Column X --> "DATA" sheet Column C 'extract status such as "DELIVERED"​

UPDATES Sheet:

Order #	Date Sent	Status	Aircraft	DETAILS	WING	Date	Pocus	Delivery Details	Signature
123456	1-Jul		AC123	Roses are red				07/12/2020 The package was received by John Smith
123457	2-Jul	RECEIVED	BC345	Violets are blue				7/4/2020
123458	3-Jul		DE256	Tow the line

DATA Sheet:

AAA	BBB	BBB	DDD	EEE	Order #	GGG	RRR	SSS	mambo	UUU	VVV	WWW	mouse	YYY	ZZZ				Date Received				Status
10000		11	Apples		123456						alpha								07/12/2020 The package was received by John Smith				Received
10001		11	Oranges		123459						beta								07/14/2020 Delivered				Del
10002		11	Lemons		123460						delta								07/13/2020 The package was received				Received
10003		11	Apples		123461						theta								07/14/2020 Delivered				Del
10004		11	Oranges		123462						gamma								7/16/2020 Picked up HJK				In Prog
10005		11	Lemons		123463						alpha								7/17/2020 Picked up Brit				In Prog
10006		11	Apples		123464						beta								Picked up 7/15/2020				In Prog

 

[ER_Neha]

Hello can you please explain the question by mentioning the names of the columns related rather than mentioning as column T,F .
as it will make easier for others to understand your query and provide better solution.

 

[NeoSez]

Hello can you please explain the question by mentioning the names of the columns related rather than mentioning as column T,F .
as it will make easier for others to understand your query and provide better solution.

Thanks for the feedback ER_Neha
I have updated the Criteria for you. I hope you can help. Thank you.

"UPDATES" sheet "ORDER # " (Column F) --> "DATA" sheet "ORDER # " (Column A) (Any new data based on these 2 columns data should be adding entire row to DATA after last used row)

EXTRACT Date ONLY (MM/DD/YYYY) from "UPDATES" sheet "Date Received" (Column T)-->"DATA" sheet "DATE" (Column G) BUT only if the word "DELIVERED" appears in "Date Received" (Col. "T").
"UPDATES" sheet "Status" (Column X) --> "DATA" sheet "Status" (Column C) 'extract status such as "DELIVERED"

 

[JLGWhiz]

UPDATES" sheet "ORDER # " (Column F) --> "DATA" sheet "ORDER # " (Column A

The sheet images you are showing had Order# in Column A for UPDATES and in Column F for DATA . Which is correcct?

 

[NeoSez]

The sheet images you are showing had Order# in Column A for UPDATES and in Column F for DATA . Which is correcct?

Hello JLGWhiz. My apologies for the confusion. It should be ORDER#. DATA is the sheet name. Thank you.

["UPDATES" sheet] ("ORDER # "-Column F) --> ["DATA" sheet] ("ORDER # "-Column A) (Any new data based on these 2 columns data should be adding entire row to DATA after last used row)

EXTRACT Date ONLY (MM/DD/YYYY) from ["UPDATES" sheet] ("Date Received"-Column T)-->["DATA" sheet] ("DATE"-Column G) BUT only if the word "DELIVERED" appears in ("Date Received"-Col. "T").

["UPDATES" sheet] ("Status"-Column X) --> ["DATA" sheet] ("Status"-Column C) 'extract status such as "DELIVERED"

 

[NeoSez]

I'm starting to think that there is no solution to find new data and overwrite existing data in a cell. I've searched the internet for weeks and haven't seen anything.

 

[ER_Neha]

Hello Hope you are doing good neosez. I tried solving your query, but got stuck , as the data provided by you and the requirement given by you, both doesn't match or I am not understanding your actual requirement. Either way can be the reason. However, I tried solving the question. Please check and let me know if it works for you.

Sub updating_the_data()
    Dim up As Worksheet
    Dim dt As Worksheet
    Dim i As Integer
    Dim j As Integer
    

    Set up = Sheets("Updates")
    Set dt = Sheets("Data")
    
    For i = 2 To dt.Cells(Rows.Count, 1).End(xlUp).Row
        j = up.Cells(Rows.Count, 1).End(xlUp).Row + 1
        If dt.Range("x" & i).Value = "Delivered" Then
            up.Range("c" & j).Value = dt.Range("x" & i).Value
            up.Range("g" & j).Value = dt.Range("t" & i).Value
            up.Range("a" & j).Value = dt.Range("f" & i).Value
        End If
    Next i
End Sub

 

[NeoSez]

Hello Hope you are doing good neosez. I tried solving your query, but got stuck , as the data provided by you and the requirement given by you, both doesn't match or I am not understanding your actual requirement. Either way can be the reason. However, I tried solving the question. Please check and let me know if it works for you.

Sub updating_the_data()
    Dim up As Worksheet
    Dim dt As Worksheet
    Dim i As Integer
    Dim j As Integer
   

    Set up = Sheets("Updates")
    Set dt = Sheets("Data")
   
    For i = 2 To dt.Cells(Rows.Count, 1).End(xlUp).Row
        j = up.Cells(Rows.Count, 1).End(xlUp).Row + 1
        If dt.Range("x" & i).Value = "Delivered" Then
            up.Range("c" & j).Value = dt.Range("x" & i).Value
            up.Range("g" & j).Value = dt.Range("t" & i).Value
            up.Range("a" & j).Value = dt.Range("f" & i).Value
        End If
    Next i
End Sub

ER_Neha Thank you so much for providing the code. However, I was unable to get it to work.

I did find this code below, on the internet. I can not get it to work. Keeps saying it's out of range. I'm too tired and I'm sure its a simple fix, but just can't see it right now.

Can someone suggest the changes required, to suit my requirement. Thank you.

Sub test()
    Dim i As Integer
    k = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To k
        findvalue = ActiveSheet.Cells(i, 1).Value
        With Sheets("Update")
            l = .Cells(Rows.Count, "a").End(xlUp).Row
            Set j = .Range("A:J").Find(findvalue)
            If Not j Is Nothing Then
                .Cells(j.Row, j.Column).Offset(0, 1).Value = Sheets("Sheet1").Cells(i, 2).Value
            Else
                .Cells(l, 1).Value = Sheets("Update").Cells(i, 1).Value
                .Cells(l, 2).Value = Sheets("Update").Cells(i, 2).Value
            End If
        End With
    Next i
End Sub