Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem transferring text value "S4" from 1 table to another
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_Ce ll1").Column iRowoffset = Sheets(shtOT).Range("OT_Schedules_Cell1").Row - _ Sheets(shtMaster).Range("All_ASSIGNED_Schedules_Ce ll1").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(iRowoffs et, -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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem transferring text value "S4" from 1 table to another
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. -- Regards, Tom Ogilvy "JimP" wrote in message om... 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_Ce ll1").Column iRowoffset = Sheets(shtOT).Range("OT_Schedules_Cell1").Row - _ Sheets(shtMaster).Range("All_ASSIGNED_Schedules_Ce ll1").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(iRowoffs et, -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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem transferring text value "S4" from 1 table to another
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 "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy "JimP" wrote in message om... 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_Ce ll1").Column iRowoffset = Sheets(shtOT).Range("OT_Schedules_Cell1").Row - _ Sheets(shtMaster).Range("All_ASSIGNED_Schedules_Ce ll1").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(iRowoffs et, -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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text "comparison" operator for "contains" used in an "IF" Function | Excel Worksheet Functions | |||
Create Pivot Table Data with Column "Sum" rather than "count" defa | Excel Discussion (Misc queries) | |||
Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) | |||
auth data not passed to "transferring file..." dialog | Excel Programming |