Hi,
Can someone help me with some codes? I have the code below that searches my raw data and places it on another sheet on specific cells, What I need now is if I edit information and I run this code, it would just edit the information on the raw data and not add it as an additional data.
Sub LookUp()
'declare the variable
Dim Dt As Range, Nn As Range, Rm As Range
Dim c As Range, orange As Range
Dim lastrow As Long
'set object variables
Set Nn = ActiveCell
Set Dt = Cells(12, Nn.Column)
Set Rm = Cells(Nn.Row, 3)
'set the variable range to loop through
lastrow = Sheet14.Range("D" & Rows.Count).End(xlUp).Row
Set orange = Sheet14.Range("D9:D" & lastrow)
'error handler
On Error GoTo errHandler:
'establish data is there and loop
If Not Range("E13:BH48") Is Nothing Then
For Each c In orange 'add dynamic range
'1. has a value 2.Verify Room 3.Look in date range 4. Check the name [optional]
If c.Value <> 0 And c.Offset(0, -1) = Rm.Value And c.Value <= Dt.Value And c.Offset(0, 2).Value >= Dt.Value Then 'And c.Offset(0, 8).Value = Nn
'add the values selectively to the top of the calendar
With Sheet12
.Range("H3").Value = c.Cells(1, -1).Value
.Range("V3").Value = c.Cells(1, 0).Value
.Range("V4").Value = c.Cells(1, 1).Value
.Range("V5").Value = c.Cells(1, 2).Value
.Range("V7").Value = c.Cells(1, 4).Value
.Range("AE3").Value = c.Cells(1, 5).Value
.Range("AE4").Value = c.Cells(1, 6).Value
.Range("AE5").Value = c.Cells(1, 7).Value
.Range("AE6").Value = c.Cells(1, 8).Value
.Range("AE7").Value = c.Cells(1, 9).Value
End With
End If
Next c
End If
'error block
On Error GoTo 0
Exit Sub
errHandler::
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
& Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Please notify the administrator"
End Sub
Bookmarks