VBA to replace cell value based on multiple criterion (numbers and dates)

[beergum]

Hi Everyone,

I need VBA help to replace content of a certain column based on multiple criterion.

If the value in the Extension Column in Sheet1 is found in the Extension column in Sheet2
AND the Date in Sheet1 equals or falls between Date From and Date To in Sheet2, replace Number in Sheet1 with the corresponding Number in Sheet2, Else, do nothing.

Here's a screenshot of the details.
Screenshot by Lightshot

And a link to a file with sample entries. http://1drv.ms/1skLYJR

I tried recording a macro doing auto filters but couldn't figure out a way to enter the corresponding number for records that meet all criteria.

Any help would be greatly appreciated.


Thanks!

 

[beergum]

Working code, done. Problem is, it runs pretty slow on the actual data I will use this for.
I only included a few rows in my sample but need to run this for over 200k rows.
Is there a way to improve the code to make it run through the data faster?

<code>Sub checkAndReplace()  Dim currentRowS1, currentRowS2 As Integer  Range("B1:A" + CStr(ThisWorkbook.Worksheets("Sheet1").UsedRange.Count) + ",A1:A" + CStr(ThisWorkbook.Worksheets("Sheet2").UsedRange.Count)).Select  For currentRowS1 = 2 To ThisWorkbook.Worksheets("Sheet1").UsedRange.Count     For currentRowS2 = 2 To ThisWorkbook.Worksheets("Sheet2").UsedRange.Count     If ThisWorkbook.Worksheets("Sheet1").Range("B" & currentRowS1).Text = ThisWorkbook.Worksheets("Sheet2").Range("A" & currentRowS2).Text Then         If DateDiff("d", ThisWorkbook.Worksheets("Sheet1").Range("A" & currentRowS1), ThisWorkbook.Worksheets("Sheet2").Range("B" & currentRowS2)) <= 0 And DateDiff("d",     ThisWorkbook.Worksheets("Sheet1").Range("A" & currentRowS1), ThisWorkbook.Worksheets("Sheet2").Range("C" & currentRowS2)) >= 0 Then         ThisWorkbook.Worksheets("Sheet1").Range("C" & currentRowS1).Value = ThisWorkbook.Worksheets("Sheet2").Range("D" & currentRowS2).Value         End If     End If      Next Next  End Sub</code></pre>

Thanks!