Problem transferring text value "S4" from 1 table to another

[JimP]

To All,

I'm attempting to find all my assigned Overtime codes within 1 table
on a specific worksheet and transfer the OT code to another sheet that
contains an identical size table (but offset: same row, +2 columns to
the right).

Whenever I attempt to transfer and/ALL cell(s) within the table that
contain the text value "S4" ... INCORRECTLY, the destination cell S4
is unintentionally cleared(?) of its content? That particular cell is
NOT within the range I'm copying FROM or Transferring too.

Any ideas why the Range("S4").value is being deleted?????

Please point me in the proper direction ...

Jim P.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private Sub OT_Merge()
Application.ScreenUpdating = False
'----------------------------
Dim shtMaster As String: shtMaster = ActiveSheet.Name
Set shNMAST = Sheets(shtMaster)
Dim shtOT As String: shtOT = Range("Name_OT").Value
Set shNOT = Sheets(shtOT)
'----------------------------
Dim Looked_Value As Variant: Dim First_Address As Variant
Dim x As Integer: x = 0: Dim cell As Range
'----------------------------
' Possible SCHEDULED OT shifts
Dim Finding_This As Variant
Finding_This = Array("S1", "S3", "S7", "S8", "S4")

' TRANSFER ALL "SCHEDULED" (S1,S3,S7,S8,S4) OT Assignments to the
MAIN View
'Row and Column offset's between the 2 (identical size) Table's on 2
different sheets
Dim iColoffset As Integer: Dim iRowoffset As Integer
iColoffset = Sheets(shtOT).Range("OT_Schedules_Cell1").Column - _
Sheets(shtMaster).Range("All_ASSIGNED_Schedules_Cell1").Column
iRowoffset = Sheets(shtOT).Range("OT_Schedules_Cell1").Row - _
Sheets(shtMaster).Range("All_ASSIGNED_Schedules_Cell1").Row
'----------------------------
shNOT.Activate
' Transfer all Scheduled OT assignments to MAIN for
viewing/calculations
Dim C As Range
''' I THINK MY PROBLEM IS WITHIN THE FOLLOWING LOOP ????
For x = 1 To 5
With Sheets(shtOT)
'OT Sheet contains 1st Table and Desired OT Value's
.Visible = True
.Activate
Looked_Value = Finding_This(x)
End With
With Sheets(shtOT).Range("OT_ALL_SHIFTS").Cells
Set C = .Find(Looked_Value, LookIn:=xlValues)
If Not C Is Nothing Then
First_Address = C.Address
Do
' Transfer all SCHEDULED OT's FROM: OT SHEET TO: MAIN Sheet
Sheets(shtMaster).Range(C.Address).Offset(iRowoffset,
-iColoffset).Value _
= Looked_Value
'Sheets(2).Range(First_Address).Select
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> First_Address
Sheets(shtMaster).Range(Looked_Value).Value = ""
End If
End With
Next x
'----------------------------
shNMAST.Activate
Range("A1").Select
End Sub

 

[Tom Ogilvy]

Sheets(shtMaster).Range(Looked_Value).Value = ""

when Looked_Value = "S4", then you clear cell S4.

You must mean to use something besides Looked_Value for the argument for
range.

 

[JimP]

Tom,
Once again ... the line you pointed out:
Sheets(shtMaster).Range(Looked_Value).Value = ""
Should have been deleted and ... well, I wish I had a good excuse why
I didn't see it, but I'd be making excuses ... appreciate the extra
set of eyes ...

JimP