Find and replace

  • Thread starter Thread starter Kash
  • Start date Start date
K

Kash

I have 3 columns in sheet totals, Date, ID & Status

and 3 cells in sheet Aug B1, B2 & B3 where we can enter Date, ID & Status
and when clicked on submit button, I need a macro to match date and ID from
sheet totals and replace Status again in sheet totals.
 
I have 3 columns in sheet totals, Date, ID & Status

and 3 cells in sheet Aug B1, B2 & B3 where we can enter Date, ID & Status
and when clicked on submit button, I need a macro to match date and ID from
sheet totals and replace Status again in sheet totals.
Click to expand...

Can you add a calculated column that contains the CONCATENATE of the
Date and ID (with a separator), e.g. =CONCATENATE(A1,"|",B1)? Then
when you do a Find(), you need to use the Value2 of the date cell in
order to get it in the same format as the cell formula:

Dim Cell As Range
Set Cell = Sheets("totals").Cells.Find(Cells(1,1).Value2 & "|" & Cells
(1,2))
Sheets("totals").Cells(Cell.Row,Cell.Column+1) = Cells(1,3)

Code is untested.

Phil Hibbs.
 
assumes D,E and F are Date,ID and Status on totals sheet. change accordingly

Option Explicit
Sub UpdateTable()

Dim aDate As Date
Dim aID As String
Dim aStatus As String
Dim bFound As Boolean

With Worksheets("aug")
aDate = .Range("B1")
aID = .Range("B2")
aStatus = .Range("B3")
End With

With Worksheets("totals")

Dim index As Long
index = 1

Do Until IsEmpty(.Cells(index, "D"))

If .Cells(index, "D") = aDate Then
If .Cells(index, "E") = aID Then
.Cells(index, "F") = aStatus
bFound = True
Exit Do
End If

End If


index = index + 1
Loop

If bFound Then
MsgBox aID & " for " & aDate & " changed to " & aStatus
Else
MsgBox aID & " for " & aDate & " was not found"
End If

End With






End Sub
 
Back
Top