Hello all,
Please help me with a formula in sheet1 D17, to do this: (please download the test file from my Drive account)
https://drive.google.com/open?id=0B9tH3lTneqQUdWkySHlqVGFNQTg
- search for 16.10.17 in sheet2, column A, and copy the corespondent number from sheet2 column D, to sheet1 column D (in cell D17)
- sheet2 will be updated daily, so maybe tomorrow i i will not have a request in 16.10.17. In this case, the request in sheet1 cell D17 must be empty too.
I just found a VBA, but it does not solve the second problem, meaning does not delete the old values.
So if today I have a request for 15.10.2017, and after the tomorrow's update I have an request in 14.10.2017 and 16.10.2017, this vba does not delete the request from 15.10.2017. The thing is that i only need to delete the data starting from smallest date in sheet2, column A... (in this case, to clear cells in sheet1, starting 16.10.2017)
I hope i made myself understood.
Thank you in advance for any help you cand give. I'm really stuck right now...
This is the code i just found:
=======================
Option Explicit
Sub ReplaceData()
Dim lastRw1, lastRw2, nxtRw, m
'Determine last row with data, Sheet1
lastRw1 = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Determine last row with data, Sheet2
lastRw2 = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
'Loop through Sheet 2, Column A
For nxtRw = 2 To lastRw2
'Search Sheet1 Column A for value from Sheet 2
With Sheets(1).Range("A2:A" & lastRw1)
Set m = .Find(Sheets(2).Range("A" & nxtRw), lookat:=xlWhole)
'Copy Sheet2 row if match is found
If Not m Is Nothing Then
Sheets(2).Range("A" & nxtRw).EntireRow.Copy _
Sheets(1).Range("A" & m.Row)
End If
End With
Next
End Sub
=======================
Please help me with a formula in sheet1 D17, to do this: (please download the test file from my Drive account)
https://drive.google.com/open?id=0B9tH3lTneqQUdWkySHlqVGFNQTg
- search for 16.10.17 in sheet2, column A, and copy the corespondent number from sheet2 column D, to sheet1 column D (in cell D17)
- sheet2 will be updated daily, so maybe tomorrow i i will not have a request in 16.10.17. In this case, the request in sheet1 cell D17 must be empty too.
I just found a VBA, but it does not solve the second problem, meaning does not delete the old values.
So if today I have a request for 15.10.2017, and after the tomorrow's update I have an request in 14.10.2017 and 16.10.2017, this vba does not delete the request from 15.10.2017. The thing is that i only need to delete the data starting from smallest date in sheet2, column A... (in this case, to clear cells in sheet1, starting 16.10.2017)
I hope i made myself understood.
Thank you in advance for any help you cand give. I'm really stuck right now...
This is the code i just found:
=======================
Option Explicit
Sub ReplaceData()
Dim lastRw1, lastRw2, nxtRw, m
'Determine last row with data, Sheet1
lastRw1 = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Determine last row with data, Sheet2
lastRw2 = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
'Loop through Sheet 2, Column A
For nxtRw = 2 To lastRw2
'Search Sheet1 Column A for value from Sheet 2
With Sheets(1).Range("A2:A" & lastRw1)
Set m = .Find(Sheets(2).Range("A" & nxtRw), lookat:=xlWhole)
'Copy Sheet2 row if match is found
If Not m Is Nothing Then
Sheets(2).Range("A" & nxtRw).EntireRow.Copy _
Sheets(1).Range("A" & m.Row)
End If
End With
Next
End Sub
=======================
It does 90% of what i need. I just don't need to clear the entire column.
