Okay, with a slightly rearranged input sheet, this code should work for you and be a little more dynamic ...
Sub Button1_Click()
Dim wsInput As Worksheet, wsData As Worksheet
Dim rngDate As Range, rngShift As Range
Dim rngLookConc As Range, rngLookDate As Range
Dim rngFind As Range, rngLoop As Range, c As Range, rngCol As Range
Dim strSearch As String, LastRow As Long
Dim i As Long, Cnt As Long, blnHide As Boolean
Set wsInput = ThisWorkbook.Sheets("Input")
Set wsData = ThisWorkbook.Sheets("Data")
Set rngLookDate = wsData.Range("3:3")
Set rngLookConc = wsData.Range("E:E")
Set rngLookNum = wsData.Range("C:C")
Set rngDate = wsInput.Range("G9")
Set rngShift = wsInput.Range("H9")
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
Set rngCol = rngLookDate.Find(rngDate.Value, LookIn:=xlValues, lookat:=xlWhole)
If rngCol Is Nothing Then
MsgBox "That date is not found on the Data sheet!", vbExclamation, "ERROR!"
GoTo EndHere
End If
LastRow = wsInput.Range("G:H").Find("*", after:=wsInput.Range("G1"), searchorder:=xlByRows, searchdirection:=xlPrevious).Row
If LastRow < 15 Then
MsgBox "You have not entered any data in the Drop/Win table!", vbExclamation, "ERROR!"
GoTo EndHere
End If
Set rngLoop = wsInput.Range("F15", wsInput.Cells(wsInput.Rows.Count, 6).End(xlUp))
Cnt = 0
blnHide = rngLookConc.EntireColumn.Hidden
rngLookConc.EntireColumn.Hidden = False
For Each c In rngLoop
strSearch = c.Value & rngShift.Value
Set rngFind = rngLookConc.Find(strSearch, MatchCase:=True)
If Not rngFind Is Nothing Then
If Len(c.Offset(0, 1).Value) = 0 Or Len(c.Offset(0, 2).Value) = 0 Then GoTo SkipCode
wsData.Cells(rngFind.Row, rngCol.Column).Value = c.Offset(0, 1).Value
wsData.Cells(rngFind.Row, rngCol.Column + 1).Value = c.Offset(0, 2).Value
wsData.Cells(rngFind.Row, rngCol.Column + 2).FormulaR1C1 = "=RC[-2]-RC[-1]"
Cnt = Cnt + 1
Set rngFind = Nothing
End If
SkipCode:
Next c
If blnHide Then rngLookConc.EntireColumn.Hidden = True
If Cnt <> 0 Then
MsgBox "A total of " & Cnt & " record(s) have been updated!", vbInformation, "Complete!"
Else
MsgBox "No values were updated!", vbInformation, "Complete!"
End If
EndHere:
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
Set rngDate = Nothing
Set rngShift = Nothing
Set rngLookConc = Nothing
Set rngLookDate = Nothing
Set rngFind = Nothing
Set c = Nothing
Set rngCol = Nothing
Set wsData = Nothing
Set wsInput = Nothing
End Sub
The changes include these small items:
On the Data sheet:
1. In columns B & C, there should be no blank rows, meaning everywhere you see a blank row it needs to equal the row above it. Just set the font to match the background color if you want.
And easy way to do this:
Select the entire range of data in B:C (B5:C108), press F5 | Special .. | Blanks, type = and then hit your Up Arrow, confirm with Ctrl + Enter. If you want to have these values static, select the column, copy it, then PasteSpecial/Values.
2. In column D, change your Swg and Gyd to Swing and Grave, just as you have it on your input sheet. You can do a Find/Replace to do this.
3. Insert a new column just after D, it will be your new column E. Starting in Row 5, enter this formula:
=B5&C5&D5
Copy down as far as the column (D) goes. (An easy way to do this is to select the cell, hover your mouse over the bottom right corner of the cell {your mouse icon will turn to a bold + sign} and double click. It is your choice to leave the value or formula in there, I would recommend formula so you don't have to remember it later. It is your choice to leave the column hidden or not, but the code will leave it in the last state it was in; IOW if the column was hidden, it will leave it that way, if it was not, the code will not hide it upon completion.
On the Input sheet:
1. Unmerge those merged cells, it plays hell trying to code for them. Your date cell should be in G9 and your Shift cell should be in H9.
2. Do not put any other data in columns G & H other than your Code/Drop/Win values.
That should be about it. Let us know if this works for you.
-----------
Regards,
Zack Barresse
