Searching for values and the adding other values
Posted by Ben on December 15, 2000 2:29 PM
How do I create a macro that searches in Column B of Sheet 2 for the value in cell J2 on Sheet 1 and when if finds it, it adds the value in cell M2 of Sheet 1 to cell D on Sheet 2 if it is January and cell E if it is February and so on until December.
Then it needs to search for the value of J2 in Column B of Sheet 3 and when it finds it, it subtracts the value in cell J2 on Sheet 1 from cell D on the same row as the value it has found, on Sheet 3.
Also, it needs to be a number not a formula such as =10-2-2-4
Thanks!
Posted by Celia on December 15, 2000 8:05 PM
Check whether the macro below does what you want. In the first paragraph of your question, I have assumed that "cell D" and "cell E" mean column D and column E on the same row as the value found.
Sub Search_etc()
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim searchRng2 As Range, foundSh2 As Range, lookFor As Range
Dim searchRng3 As Range, foundSh3 As Range, valSh2 As Range
Dim mthCol%
mthCol = Month(Now()) + 3
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")
Set lookFor = ws1.Range("J2")
Set valSh2 = ws1.Range("M2")
Set searchRng2 = ws2.Range("B:B")
Set searchRng3 = ws3.Range("B:B")
Set foundSh2 = searchRng2.Cells.Find(What:=lookFor, _
After:=searchRng2.Cells(1), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False)
Set foundSh3 = searchRng3.Cells.Find(What:=lookFor, _
After:=searchRng3.Cells(1), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False)
If foundSh2 Is Nothing Then
MsgBox "There is no match on Sheet2"
Else
ws2.Cells(foundSh2.Row, mthCol).Value = _
ws2.Cells(foundSh2.Row, mthCol).Value + _
valSh2.Value
End If
If foundSh3 Is Nothing Then
MsgBox "There is no match on Sheet3"
Else
ws3.Cells(foundSh3.Row, 4).Value = _
ws3.Cells(foundSh3.Row, 4).Value - _
lookFor.Value
End If
End Sub
Celia
