Change Reference to Columns in a Macro
hi, is there a way to modify the reference to columns,
to be from a different single cell, such as INDIRECT.. within a macro / script? Thanks the type of lines I want to reference a Range("A1").Value Columns("B:C").Select Range("D:E").Select Intersect(Me.Range("F:G"), With Me.Cells(.Row, "H") |
Change Reference to Columns in a Macro
Hi,
I have read your question many times and still I am not sure if I am correctly interpreting what it is that you want. I am assuming that you want to reference Cells and Ranges using variables instead of hard coding the actual Cell/Range address. Have a look at the following examples of referencing cells and ranges in VBA code and see if it answers your question. Note: When using Cells function to reference cells, the row is first followed by the column. That is Cells(RowNumber,ColNumber). This is back to front to the way they are referenced with Range(ColId : RowNumb) Dim lngRowNumber As Long Dim lngColNumber As Long Dim strCol1 As String Dim strCol2 As String lngRowNumber = 4 lngColNumber = 5 'Following same as Range("E5").Select Cells(lngRowNumber, lngColNumber).Select 'Following same as Range("E4:G6").Select Range(Cells(lngRowNumber, lngColNumber), Cells(6, 7)).Select strCol1 = "F" 'Following same as Range("F:G").Select Range(strCol1 & ":G").Select strCol2 = "H" 'Following same as Range("F:H").select Range(strCol1 & ":" & strCol2).Select -- Regards, OssieMac "Nastech" wrote: hi, is there a way to modify the reference to columns, to be from a different single cell, such as INDIRECT.. within a macro / script? Thanks the type of lines I want to reference a Range("A1").Value Columns("B:C").Select Range("D:E").Select Intersect(Me.Range("F:G"), With Me.Cells(.Row, "H") |
Change Reference to Columns in a Macro
If I understand you correctly, this concept should work.
Lets say that the column identifier you want to change is on a worksheet named Sheet1 and it is in cell A1. Then your code can do something like either of these examples: Dim myColumns As String myColumns = ThisWorkbook.Worksheets("Sheet1").Range("A1") 'assuming that cell A1 contained B:C then you dould do this 'you must activate the proper sheet first Worksheets("Sheet2").Activate Columns(myColumns).Select and that would select columns A:C on Sheet2 You could even write it without "myColumns" this way Worksheets("Sheet2").Activate ThisWorkbook.Worksheets("Sheet1").Range("A1") Columns(ThisWorkbook.Worksheets("Sheet1").Range("A 1")).Select Hope this helps you with your problem. "Nastech" wrote: hi, is there a way to modify the reference to columns, to be from a different single cell, such as INDIRECT.. within a macro / script? Thanks the type of lines I want to reference a Range("A1").Value Columns("B:C").Select Range("D:E").Select Intersect(Me.Range("F:G"), With Me.Cells(.Row, "H") |
Change Reference to Columns in a Macro
hi, thanks for responses. sorry if questions sometimes vague. am not sure
what is pertinent vs. posting too much. (work on advanced items?.. slower with some area's novice: spend much time to develope..). :) not even sure if your answers apply.. new to scripting.. macro's, you may need to tell me if your anwers apply to what doing, as not sure. thanks. Will include script below that references, mostly columns. work-around is for some kind of INDIRECT() reference to desired cells, from cells that will not be moved, i.e. programming will never have to change.. ?? (not sure on that), by use of either just typing the COLUMN LETTER reference in cells listed (B2 C2, B3 C3 etc), OR with next formula use as a more "DYNAMIC" response: =SUBSTITUTE(SUBSTITUTE(CELL("address",$DU2),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$DU2),"$",""),ROW(),"") gets: DU:DU so if need any else such as quotes.. around "DU:DU", I can add to that formula as well. IF YOUR ANSWERS DO the same thing.. or apply to what doing you can let me know, will work on but take some time for me to try. PROBLEM: when add/ remove columns, have to manually change script. SUBSTITUTE formula is work around. Script using is: Option Explicit Private Sub CommandButton1_Click() If Range("DN6").Value = "Z" Then '1 col: copy Paste-Values to left 1 col Columns("DU:DU").Select Selection.Copy Range("DT:DT").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '22 col: (main, 21 col back up), COPY: Paste-Values to right 1 col Columns("EE:EY").Select Selection.Copy Range("EF1").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '20 col: (10 sets of 2), COPY: Paste-Values to right 2 cols Columns("FE:FV").Select Selection.Copy Range("FG:FX").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False 'double col: (1 set of 2), COPY: Paste-Values to different section Columns("EC:ED").Select Selection.Copy Range("FE:FF").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False End If End Sub Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Target.Row < 130 Then Exit Sub If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'add "+" to blank spaces col A: If Not Intersect(Me.Range("a:a"), .Cells) Is Nothing Then Application.EnableEvents = False .Value = Replace(.Value, " ", "+") Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CK:CO"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination: With Me.Cells(.Row, "CF") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CW:CW"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination With Me.Cells(.Row, "CG") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If End With End Sub "OssieMac" wrote: Hi, I have read your question many times and still I am not sure if I am correctly interpreting what it is that you want. I am assuming that you want to reference Cells and Ranges using variables instead of hard coding the actual Cell/Range address. Have a look at the following examples of referencing cells and ranges in VBA code and see if it answers your question. Note: When using Cells function to reference cells, the row is first followed by the column. That is Cells(RowNumber,ColNumber). This is back to front to the way they are referenced with Range(ColId : RowNumb) Dim lngRowNumber As Long Dim lngColNumber As Long Dim strCol1 As String Dim strCol2 As String lngRowNumber = 4 lngColNumber = 5 'Following same as Range("E5").Select Cells(lngRowNumber, lngColNumber).Select 'Following same as Range("E4:G6").Select Range(Cells(lngRowNumber, lngColNumber), Cells(6, 7)).Select strCol1 = "F" 'Following same as Range("F:G").Select Range(strCol1 & ":G").Select strCol2 = "H" 'Following same as Range("F:H").select Range(strCol1 & ":" & strCol2).Select -- Regards, OssieMac "Nastech" wrote: hi, is there a way to modify the reference to columns, to be from a different single cell, such as INDIRECT.. within a macro / script? Thanks the type of lines I want to reference a Range("A1").Value Columns("B:C").Select Range("D:E").Select Intersect(Me.Range("F:G"), With Me.Cells(.Row, "H") |
Change Reference to Columns in a Macro
hi, thanks for responses. sorry if questions sometimes vague. am not sure
what is pertinent vs. posting too much. (work on advanced items?.. slower with some area's novice: spend much time to develope..). :) not even sure if your answers apply.. new to scripting.. macro's, you may need to tell me if your anwers apply to what doing, as not sure. thanks. Will include script below that references, mostly columns. work-around is for some kind of INDIRECT() reference to desired cells, from cells that will not be moved, i.e. programming will never have to change.. ?? (not sure on that), by use of either just typing the COLUMN LETTER reference in cells listed (B2 C2, B3 C3 etc), OR with next formula use as a more "DYNAMIC" response: =SUBSTITUTE(SUBSTITUTE(CELL("address",$DU2),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$DU2),"$",""),ROW(),"") gets: DU:DU so if need any else such as quotes.. around "DU:DU", I can add to that formula as well. IF YOUR ANSWERS DO the same thing.. or apply to what doing you can let me know, will work on but take some time for me to try. PROBLEM: when add/ remove columns, have to manually change script. SUBSTITUTE formula is work around. Script using is: Option Explicit Private Sub CommandButton1_Click() If Range("DN6").Value = "Z" Then '1 col: copy Paste-Values to left 1 col Columns("DU:DU").Select Selection.Copy Range("DT:DT").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '22 col: (main, 21 col back up), COPY: Paste-Values to right 1 col Columns("EE:EY").Select Selection.Copy Range("EF1").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '20 col: (10 sets of 2), COPY: Paste-Values to right 2 cols Columns("FE:FV").Select Selection.Copy Range("FG:FX").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False 'double col: (1 set of 2), COPY: Paste-Values to different section Columns("EC:ED").Select Selection.Copy Range("FE:FF").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False End If End Sub Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Target.Row < 130 Then Exit Sub If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'add "+" to blank spaces col A: If Not Intersect(Me.Range("a:a"), .Cells) Is Nothing Then Application.EnableEvents = False .Value = Replace(.Value, " ", "+") Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CK:CO"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination: With Me.Cells(.Row, "CF") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CW:CW"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination With Me.Cells(.Row, "CG") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If End With End Sub "JLatham" wrote: If I understand you correctly, this concept should work. Lets say that the column identifier you want to change is on a worksheet named Sheet1 and it is in cell A1. Then your code can do something like either of these examples: Dim myColumns As String myColumns = ThisWorkbook.Worksheets("Sheet1").Range("A1") 'assuming that cell A1 contained B:C then you dould do this 'you must activate the proper sheet first Worksheets("Sheet2").Activate Columns(myColumns).Select and that would select columns A:C on Sheet2 You could even write it without "myColumns" this way Worksheets("Sheet2").Activate ThisWorkbook.Worksheets("Sheet1").Range("A1") Columns(ThisWorkbook.Worksheets("Sheet1").Range("A 1")).Select Hope this helps you with your problem. "Nastech" wrote: hi, is there a way to modify the reference to columns, to be from a different single cell, such as INDIRECT.. within a macro / script? Thanks the type of lines I want to reference a Range("A1").Value Columns("B:C").Select Range("D:E").Select Intersect(Me.Range("F:G"), With Me.Cells(.Row, "H") |
Change Reference to Columns in a Macro
I have 2 basic questions at this point:
#1 - are you wanting a formula solution or a code solution? I am under the impression that you are looking for a code (macro) solution. #2 - in your code you begin with a test for DN6 = "Z" - will you ever have to look in any other cell for the "Z"? I am going to rewrite yout first Sub (CommandButton1_Click) so that it gets the ranges/addresses used from other cells on the same sheet and will post that back later. "Nastech" wrote: hi, thanks for responses. sorry if questions sometimes vague. am not sure what is pertinent vs. posting too much. (work on advanced items?.. slower with some area's novice: spend much time to develope..). :) not even sure if your answers apply.. new to scripting.. macro's, you may need to tell me if your anwers apply to what doing, as not sure. thanks. Will include script below that references, mostly columns. work-around is for some kind of INDIRECT() reference to desired cells, from cells that will not be moved, i.e. programming will never have to change.. ?? (not sure on that), by use of either just typing the COLUMN LETTER reference in cells listed (B2 C2, B3 C3 etc), OR with next formula use as a more "DYNAMIC" response: =SUBSTITUTE(SUBSTITUTE(CELL("address",$DU2),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$DU2),"$",""),ROW(),"") gets: DU:DU so if need any else such as quotes.. around "DU:DU", I can add to that formula as well. IF YOUR ANSWERS DO the same thing.. or apply to what doing you can let me know, will work on but take some time for me to try. PROBLEM: when add/ remove columns, have to manually change script. SUBSTITUTE formula is work around. Script using is: Option Explicit Private Sub CommandButton1_Click() If Range("DN6").Value = "Z" Then '1 col: copy Paste-Values to left 1 col Columns("DU:DU").Select Selection.Copy Range("DT:DT").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '22 col: (main, 21 col back up), COPY: Paste-Values to right 1 col Columns("EE:EY").Select Selection.Copy Range("EF1").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '20 col: (10 sets of 2), COPY: Paste-Values to right 2 cols Columns("FE:FV").Select Selection.Copy Range("FG:FX").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False 'double col: (1 set of 2), COPY: Paste-Values to different section Columns("EC:ED").Select Selection.Copy Range("FE:FF").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False End If End Sub Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Target.Row < 130 Then Exit Sub If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'add "+" to blank spaces col A: If Not Intersect(Me.Range("a:a"), .Cells) Is Nothing Then Application.EnableEvents = False .Value = Replace(.Value, " ", "+") Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CK:CO"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination: With Me.Cells(.Row, "CF") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CW:CW"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination With Me.Cells(.Row, "CG") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If End With End Sub "JLatham" wrote: If I understand you correctly, this concept should work. Lets say that the column identifier you want to change is on a worksheet named Sheet1 and it is in cell A1. Then your code can do something like either of these examples: Dim myColumns As String myColumns = ThisWorkbook.Worksheets("Sheet1").Range("A1") 'assuming that cell A1 contained B:C then you dould do this 'you must activate the proper sheet first Worksheets("Sheet2").Activate Columns(myColumns).Select and that would select columns A:C on Sheet2 You could even write it without "myColumns" this way Worksheets("Sheet2").Activate ThisWorkbook.Worksheets("Sheet1").Range("A1") Columns(ThisWorkbook.Worksheets("Sheet1").Range("A 1")).Select Hope this helps you with your problem. "Nastech" wrote: hi, is there a way to modify the reference to columns, to be from a different single cell, such as INDIRECT.. within a macro / script? Thanks the type of lines I want to reference a Range("A1").Value Columns("B:C").Select Range("D:E").Select Intersect(Me.Range("F:G"), With Me.Cells(.Row, "H") |
Change Reference to Columns in a Macro
Hi Nastech,
What about using Find to locate something that never changes in the column/s. For example the column header. The following examples demonstrate a couple of methods. See the comments in the code for more information as to what they do. Sub Macro1() 'The following finds the column header and 'sets a variable that can be used in lieu 'of the cell range. Dim objCol As Object Dim strToFind As String strToFind = "My Col Header 1" Set objCol = Cells.Find _ (What:=strToFind, _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) 'Test that string found and re-set 'variable to entire column If Not objCol Is Nothing Then Set objCol = objCol.EntireColumn 'Following line is example of using the variable 'in lieu of the range. objCol.Select Else MsgBox strToFind & " not found" End If 'The following names the range after the above Find. 'The named range can then be used in lieu 'of the actual range. 'Note: Named ranges are saved with the WorkBook. 'It is the same as naming a range in the 'interactive mode on the worksheet. If Not objCol Is Nothing Then ActiveWorkbook.Names.Add Name:="MyFirstCol", _ RefersToR1C1:=objCol.EntireColumn 'Following is example of using the named 'range in lieu of the actual range Range("MyFirstCol").Select Else MsgBox strToFind & " not found" End If End Sub -- Regards, OssieMac "Nastech" wrote: hi, thanks for responses. sorry if questions sometimes vague. am not sure what is pertinent vs. posting too much. (work on advanced items?.. slower with some area's novice: spend much time to develope..). :) not even sure if your answers apply.. new to scripting.. macro's, you may need to tell me if your anwers apply to what doing, as not sure. thanks. Will include script below that references, mostly columns. work-around is for some kind of INDIRECT() reference to desired cells, from cells that will not be moved, i.e. programming will never have to change.. ?? (not sure on that), by use of either just typing the COLUMN LETTER reference in cells listed (B2 C2, B3 C3 etc), OR with next formula use as a more "DYNAMIC" response: =SUBSTITUTE(SUBSTITUTE(CELL("address",$DU2),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$DU2),"$",""),ROW(),"") gets: DU:DU so if need any else such as quotes.. around "DU:DU", I can add to that formula as well. IF YOUR ANSWERS DO the same thing.. or apply to what doing you can let me know, will work on but take some time for me to try. PROBLEM: when add/ remove columns, have to manually change script. SUBSTITUTE formula is work around. Script using is: Option Explicit Private Sub CommandButton1_Click() If Range("DN6").Value = "Z" Then '1 col: copy Paste-Values to left 1 col Columns("DU:DU").Select Selection.Copy Range("DT:DT").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '22 col: (main, 21 col back up), COPY: Paste-Values to right 1 col Columns("EE:EY").Select Selection.Copy Range("EF1").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '20 col: (10 sets of 2), COPY: Paste-Values to right 2 cols Columns("FE:FV").Select Selection.Copy Range("FG:FX").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False 'double col: (1 set of 2), COPY: Paste-Values to different section Columns("EC:ED").Select Selection.Copy Range("FE:FF").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False End If End Sub Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Target.Row < 130 Then Exit Sub If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'add "+" to blank spaces col A: If Not Intersect(Me.Range("a:a"), .Cells) Is Nothing Then Application.EnableEvents = False .Value = Replace(.Value, " ", "+") Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CK:CO"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination: With Me.Cells(.Row, "CF") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CW:CW"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination With Me.Cells(.Row, "CG") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If End With End Sub "OssieMac" wrote: Hi, I have read your question many times and still I am not sure if I am correctly interpreting what it is that you want. I am assuming that you want to reference Cells and Ranges using variables instead of hard coding the actual Cell/Range address. Have a look at the following examples of referencing cells and ranges in VBA code and see if it answers your question. Note: When using Cells function to reference cells, the row is first followed by the column. That is Cells(RowNumber,ColNumber). This is back to front to the way they are referenced with Range(ColId : RowNumb) Dim lngRowNumber As Long Dim lngColNumber As Long Dim strCol1 As String Dim strCol2 As String lngRowNumber = 4 lngColNumber = 5 'Following same as Range("E5").Select Cells(lngRowNumber, lngColNumber).Select 'Following same as Range("E4:G6").Select Range(Cells(lngRowNumber, lngColNumber), Cells(6, 7)).Select strCol1 = "F" 'Following same as Range("F:G").Select Range(strCol1 & ":G").Select strCol2 = "H" 'Following same as Range("F:H").select Range(strCol1 & ":" & strCol2).Select -- Regards, OssieMac "Nastech" wrote: hi, is there a way to modify the reference to columns, to be from a different single cell, such as INDIRECT.. within a macro / script? Thanks the type of lines I want to reference a Range("A1").Value Columns("B:C").Select Range("D:E").Select Intersect(Me.Range("F:G"), With Me.Cells(.Row, "H") |
Change Reference to Columns in a Macro
I intended to add that objCol.Column will return the column number after the Find. -- Regards, OssieMac |
Change Reference to Columns in a Macro
Here is the modified code, I used cells A1 through F1 on the same sheet to
put the various column references, but you could use other cells as long as their addresses remain constant, you could even put them on another sheet. That's explained in the code below. I hope this helps with the situation. Private Sub CommandButton1_Click() 'to prevent confusion with code like Range(Range("A1")) 'we will move our information from cells A1 through F1 'into variables that we will use in the code below 'note that within the cells on the worksheet, the 'double quote marks are NOT used, so in cell A1 'you would actually just enter DN6 not "DN6" 'and in B1 you would enter DU:DU, not "DU:DU" 'changing the contents of those cells will change 'the way the code operates. Dim testCellAddress As String ' will hold "DN6" from A1 Dim singleColumnID As String ' will hold "DU:DU" from B1 Dim groupOneColumnID As String ' will hold "EE:EY" from C1 Dim groupTwoColumnID As String ' will hold "FE:FV" from D1 Dim groupThreeSourceID As String ' will hold "EC:ED" from E1 Dim groupThreeDestinationID As String ' will hold "FE:FF" from F1 'get the values from the active sheet 'if the cells are on a sheet where columns may be added or 'deleted, then they need to be in cells to the left of the 'any column that might get moved. In other words, these 'address must remain stable. You could put the information 'on another sheet and reference them in a fashion similar to ' testCellAddress=Worksheets("AnotherSheetName").Ran ge("A1") testCellAddress = Range("A1") ' .Value is implied singleColumnID = Range("B1") groupOneColumnID = Range("C1") groupTwoColumnID = Range("D1") groupThreeSourceID = Range("E1") groupThreeDestinationID = Range("F1") If Range(testCellAddress).Value = "Z" Then '1 col: copy Paste-Values to left 1 col Columns(singleColumnID).Select Selection.Copy Range(singleColumnID).Offset(0, 1).Select ' 1 column to the right ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '22 col: (main, 21 col back up), COPY: Paste-Values to right 1 col Columns(groupOneColumnID).Select Selection.Copy Range(groupOneColumnID).Offset(0, 1).Select ' 1 column to the right ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '20 col: (10 sets of 2), COPY: Paste-Values to right 2 cols Columns(groupTwoColumnID).Select Selection.Copy Range(groupTwoColumnID).Offset(0, 2).Select ' 2 columns to the right ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False 'double col: (1 set of 2), COPY: Paste-Values to different section Columns(groupThreeSourceID).Select Selection.Copy Range(groupThreeDestinationID).Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False End If End Sub "Nastech" wrote: hi, thanks for responses. sorry if questions sometimes vague. am not sure what is pertinent vs. posting too much. (work on advanced items?.. slower with some area's novice: spend much time to develope..). :) not even sure if your answers apply.. new to scripting.. macro's, you may need to tell me if your anwers apply to what doing, as not sure. thanks. Will include script below that references, mostly columns. work-around is for some kind of INDIRECT() reference to desired cells, from cells that will not be moved, i.e. programming will never have to change.. ?? (not sure on that), by use of either just typing the COLUMN LETTER reference in cells listed (B2 C2, B3 C3 etc), OR with next formula use as a more "DYNAMIC" response: =SUBSTITUTE(SUBSTITUTE(CELL("address",$DU2),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$DU2),"$",""),ROW(),"") gets: DU:DU so if need any else such as quotes.. around "DU:DU", I can add to that formula as well. IF YOUR ANSWERS DO the same thing.. or apply to what doing you can let me know, will work on but take some time for me to try. PROBLEM: when add/ remove columns, have to manually change script. SUBSTITUTE formula is work around. Script using is: Option Explicit Private Sub CommandButton1_Click() If Range("DN6").Value = "Z" Then '1 col: copy Paste-Values to left 1 col Columns("DU:DU").Select Selection.Copy Range("DT:DT").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '22 col: (main, 21 col back up), COPY: Paste-Values to right 1 col Columns("EE:EY").Select Selection.Copy Range("EF1").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '20 col: (10 sets of 2), COPY: Paste-Values to right 2 cols Columns("FE:FV").Select Selection.Copy Range("FG:FX").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False 'double col: (1 set of 2), COPY: Paste-Values to different section Columns("EC:ED").Select Selection.Copy Range("FE:FF").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False End If End Sub Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Target.Row < 130 Then Exit Sub If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'add "+" to blank spaces col A: If Not Intersect(Me.Range("a:a"), .Cells) Is Nothing Then Application.EnableEvents = False .Value = Replace(.Value, " ", "+") Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CK:CO"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination: With Me.Cells(.Row, "CF") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CW:CW"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination With Me.Cells(.Row, "CG") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If End With End Sub "JLatham" wrote: If I understand you correctly, this concept should work. Lets say that the column identifier you want to change is on a worksheet named Sheet1 and it is in cell A1. Then your code can do something like either of these examples: Dim myColumns As String myColumns = ThisWorkbook.Worksheets("Sheet1").Range("A1") 'assuming that cell A1 contained B:C then you dould do this 'you must activate the proper sheet first Worksheets("Sheet2").Activate Columns(myColumns).Select and that would select columns A:C on Sheet2 You could even write it without "myColumns" this way Worksheets("Sheet2").Activate ThisWorkbook.Worksheets("Sheet1").Range("A1") Columns(ThisWorkbook.Worksheets("Sheet1").Range("A 1")).Select Hope this helps you with your problem. "Nastech" wrote: hi, is there a way to modify the reference to columns, to be from a different single cell, such as INDIRECT.. within a macro / script? Thanks the type of lines I want to reference a Range("A1").Value Columns("B:C").Select Range("D:E").Select Intersect(Me.Range("F:G"), With Me.Cells(.Row, "H") |
Change Reference to Columns in a Macro
hi, thanks both for replies, not sure if I have to post twice to send, but
for this, am finishing code base/basics before could get some pro-help with combining this with another file macro-code. (novice/ no macro experience) but some of.. 1. macro code needed to automate small copy-paste task, sub to other sheet download task. 2. is a lock to keep from hitting button by accident / will use same button for download ("X", later). missed getting clearing "Z" out of box after copy-paste, but figure can get that myself, believe there was a macro making tool in excel. **** help is greatly appreciated. work ~2 full time jobs, was trying to get this info all week. and.. haven't had chance to try/ look at closely yet. here I :) would say darn, after all posts previous, missed putting in some of info previously supplied, but imagine I can figure that out easy enough with all that has been supplied. would think items should be on same sheet (if a fault 13MB: constantly fine-tuning out uneeded variables; my sheet is basically a 1 sheet setup for now). will follow-up with "work" with someone offline, after I get to that.. thanks. previous info: which all output, in sheet, as follows (related to script using below).. DN6 in cell B1 DU:DU DT:DT B2 C2 EE:EY EF1 B3 C3 FE:FV FG:FX B4 C4 EC:ED FE:FF B5 C5 CK:CO CF B6 C6 CW:CW CG B7 C7 "JLatham" wrote: I have 2 basic questions at this point: #1 - are you wanting a formula solution or a code solution? I am under the impression that you are looking for a code (macro) solution. #2 - in your code you begin with a test for DN6 = "Z" - will you ever have to look in any other cell for the "Z"? I am going to rewrite yout first Sub (CommandButton1_Click) so that it gets the ranges/addresses used from other cells on the same sheet and will post that back later. "Nastech" wrote: hi, thanks for responses. sorry if questions sometimes vague. am not sure what is pertinent vs. posting too much. (work on advanced items?.. slower with some area's novice: spend much time to develope..). :) not even sure if your answers apply.. new to scripting.. macro's, you may need to tell me if your anwers apply to what doing, as not sure. thanks. Will include script below that references, mostly columns. work-around is for some kind of INDIRECT() reference to desired cells, from cells that will not be moved, i.e. programming will never have to change.. ?? (not sure on that), by use of either just typing the COLUMN LETTER reference in cells listed (B2 C2, B3 C3 etc), OR with next formula use as a more "DYNAMIC" response: =SUBSTITUTE(SUBSTITUTE(CELL("address",$DU2),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$DU2),"$",""),ROW(),"") gets: DU:DU so if need any else such as quotes.. around "DU:DU", I can add to that formula as well. IF YOUR ANSWERS DO the same thing.. or apply to what doing you can let me know, will work on but take some time for me to try. PROBLEM: when add/ remove columns, have to manually change script. SUBSTITUTE formula is work around. Script using is: Option Explicit Private Sub CommandButton1_Click() If Range("DN6").Value = "Z" Then '1 col: copy Paste-Values to left 1 col Columns("DU:DU").Select Selection.Copy Range("DT:DT").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '22 col: (main, 21 col back up), COPY: Paste-Values to right 1 col Columns("EE:EY").Select Selection.Copy Range("EF1").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '20 col: (10 sets of 2), COPY: Paste-Values to right 2 cols Columns("FE:FV").Select Selection.Copy Range("FG:FX").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False 'double col: (1 set of 2), COPY: Paste-Values to different section Columns("EC:ED").Select Selection.Copy Range("FE:FF").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False End If End Sub Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Target.Row < 130 Then Exit Sub If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'add "+" to blank spaces col A: If Not Intersect(Me.Range("a:a"), .Cells) Is Nothing Then Application.EnableEvents = False .Value = Replace(.Value, " ", "+") Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CK:CO"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination: With Me.Cells(.Row, "CF") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CW:CW"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination With Me.Cells(.Row, "CG") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If End With End Sub "JLatham" wrote: If I understand you correctly, this concept should work. Lets say that the column identifier you want to change is on a worksheet named Sheet1 and it is in cell A1. Then your code can do something like either of these examples: Dim myColumns As String myColumns = ThisWorkbook.Worksheets("Sheet1").Range("A1") 'assuming that cell A1 contained B:C then you dould do this 'you must activate the proper sheet first Worksheets("Sheet2").Activate Columns(myColumns).Select and that would select columns A:C on Sheet2 You could even write it without "myColumns" this way Worksheets("Sheet2").Activate ThisWorkbook.Worksheets("Sheet1").Range("A1") Columns(ThisWorkbook.Worksheets("Sheet1").Range("A 1")).Select Hope this helps you with your problem. "Nastech" wrote: hi, is there a way to modify the reference to columns, to be from a different single cell, such as INDIRECT.. within a macro / script? Thanks the type of lines I want to reference a Range("A1").Value Columns("B:C").Select Range("D:E").Select Intersect(Me.Range("F:G"), With Me.Cells(.Row, "H") |
Change Reference to Columns in a Macro
I will check these out, am novice to macro's, but guess that using column
letters is would would refer to/ think that is what your stuff refers to, column names change. have posted other info below. may take me a day, or a week to test. will repost. Help is greatly appreciated. strToFind = "My Col Header 1" refers to my column name for destination? novice enough to not know what is better for performance? / if matters that have multiple items : to what takes les space, if pertinent. will see if I can apply / test. thanks again. "OssieMac" wrote: I intended to add that objCol.Column will return the column number after the Find. -- Regards, OssieMac |
Change Reference to Columns in a Macro
hi, seems to work 99%, does all the functions but gets to end and get a
Runtime Error: This operation requires the merged cells to be identically sized. it selects area to left of every thing doing, between column B / DT. (CF:CO).. anyways, have some cells in header merged (must have), is there a way to skip that debug situation? rates.. have other work. "JLatham" wrote: Here is the modified code, I used cells A1 through F1 on the same sheet to put the various column references, but you could use other cells as long as their addresses remain constant, you could even put them on another sheet. That's explained in the code below. I hope this helps with the situation. Private Sub CommandButton1_Click() 'to prevent confusion with code like Range(Range("A1")) 'we will move our information from cells A1 through F1 'into variables that we will use in the code below 'note that within the cells on the worksheet, the 'double quote marks are NOT used, so in cell A1 'you would actually just enter DN6 not "DN6" 'and in B1 you would enter DU:DU, not "DU:DU" 'changing the contents of those cells will change 'the way the code operates. Dim testCellAddress As String ' will hold "DN6" from A1 Dim singleColumnID As String ' will hold "DU:DU" from B1 Dim groupOneColumnID As String ' will hold "EE:EY" from C1 Dim groupTwoColumnID As String ' will hold "FE:FV" from D1 Dim groupThreeSourceID As String ' will hold "EC:ED" from E1 Dim groupThreeDestinationID As String ' will hold "FE:FF" from F1 'get the values from the active sheet 'if the cells are on a sheet where columns may be added or 'deleted, then they need to be in cells to the left of the 'any column that might get moved. In other words, these 'address must remain stable. You could put the information 'on another sheet and reference them in a fashion similar to ' testCellAddress=Worksheets("AnotherSheetName").Ran ge("A1") testCellAddress = Range("A1") ' .Value is implied singleColumnID = Range("B1") groupOneColumnID = Range("C1") groupTwoColumnID = Range("D1") groupThreeSourceID = Range("E1") groupThreeDestinationID = Range("F1") If Range(testCellAddress).Value = "Z" Then '1 col: copy Paste-Values to left 1 col Columns(singleColumnID).Select Selection.Copy Range(singleColumnID).Offset(0, 1).Select ' 1 column to the right ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '22 col: (main, 21 col back up), COPY: Paste-Values to right 1 col Columns(groupOneColumnID).Select Selection.Copy Range(groupOneColumnID).Offset(0, 1).Select ' 1 column to the right ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '20 col: (10 sets of 2), COPY: Paste-Values to right 2 cols Columns(groupTwoColumnID).Select Selection.Copy Range(groupTwoColumnID).Offset(0, 2).Select ' 2 columns to the right ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False 'double col: (1 set of 2), COPY: Paste-Values to different section Columns(groupThreeSourceID).Select Selection.Copy Range(groupThreeDestinationID).Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False End If End Sub "Nastech" wrote: hi, thanks for responses. sorry if questions sometimes vague. am not sure what is pertinent vs. posting too much. (work on advanced items?.. slower with some area's novice: spend much time to develope..). :) not even sure if your answers apply.. new to scripting.. macro's, you may need to tell me if your anwers apply to what doing, as not sure. thanks. Will include script below that references, mostly columns. work-around is for some kind of INDIRECT() reference to desired cells, from cells that will not be moved, i.e. programming will never have to change.. ?? (not sure on that), by use of either just typing the COLUMN LETTER reference in cells listed (B2 C2, B3 C3 etc), OR with next formula use as a more "DYNAMIC" response: =SUBSTITUTE(SUBSTITUTE(CELL("address",$DU2),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$DU2),"$",""),ROW(),"") gets: DU:DU so if need any else such as quotes.. around "DU:DU", I can add to that formula as well. IF YOUR ANSWERS DO the same thing.. or apply to what doing you can let me know, will work on but take some time for me to try. PROBLEM: when add/ remove columns, have to manually change script. SUBSTITUTE formula is work around. Script using is: Option Explicit Private Sub CommandButton1_Click() If Range("DN6").Value = "Z" Then '1 col: copy Paste-Values to left 1 col Columns("DU:DU").Select Selection.Copy Range("DT:DT").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '22 col: (main, 21 col back up), COPY: Paste-Values to right 1 col Columns("EE:EY").Select Selection.Copy Range("EF1").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '20 col: (10 sets of 2), COPY: Paste-Values to right 2 cols Columns("FE:FV").Select Selection.Copy Range("FG:FX").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False 'double col: (1 set of 2), COPY: Paste-Values to different section Columns("EC:ED").Select Selection.Copy Range("FE:FF").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False End If End Sub Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Target.Row < 130 Then Exit Sub If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'add "+" to blank spaces col A: If Not Intersect(Me.Range("a:a"), .Cells) Is Nothing Then Application.EnableEvents = False .Value = Replace(.Value, " ", "+") Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CK:CO"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination: With Me.Cells(.Row, "CF") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CW:CW"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination With Me.Cells(.Row, "CG") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If End With End Sub "JLatham" wrote: If I understand you correctly, this concept should work. Lets say that the column identifier you want to change is on a worksheet named Sheet1 and it is in cell A1. Then your code can do something like either of these examples: Dim myColumns As String myColumns = ThisWorkbook.Worksheets("Sheet1").Range("A1") 'assuming that cell A1 contained B:C then you dould do this 'you must activate the proper sheet first Worksheets("Sheet2").Activate Columns(myColumns).Select and that would select columns A:C on Sheet2 You could even write it without "myColumns" this way Worksheets("Sheet2").Activate ThisWorkbook.Worksheets("Sheet1").Range("A1") Columns(ThisWorkbook.Worksheets("Sheet1").Range("A 1")).Select Hope this helps you with your problem. "Nastech" wrote: hi, is there a way to modify the reference to columns, to be from a different single cell, such as INDIRECT.. within a macro / script? Thanks the type of lines I want to reference a Range("A1").Value Columns("B:C").Select Range("D:E").Select Intersect(Me.Range("F:G"), With Me.Cells(.Row, "H") |
Change Reference to Columns in a Macro
hi, scratch last note on error, had selected wrong cells in one of my fixed/
absolute cell locations. the script works great. thanks. for others: as using in/as reference cells: (for dynamic reference for script to changing column/cell locations) in cell: B1 gets: DN6 =SUBSTITUTE(SUBSTITUTE(CELL("address",$DN$6),"$"," "),"","") in cell: B2 gets: DU:DU, etc. for other columns =SUBSTITUTE(SUBSTITUTE(CELL("address",$DU2),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$DU2),"$",""),ROW(),"") script for moving data: (copy, paste-special-values); other script for putting dates in columns when entering data, etc. Option Explicit Private Sub CommandButton1_Click() Dim testCellAddress As String ' will hold "DN6" from B1 Dim singleColumnID As String ' will hold "DU:DU" from B2 Dim groupOneColumnID As String ' will hold "EE:EY" from B3 Dim groupTwoColumnID As String ' will hold "FE:FV" from B4 Dim groupThreeSourceID As String ' will hold "EC:ED" from B5 Dim groupThreeDestinationID As String ' will hold "FE:FF" from B6 'get the values from the active sheet. address must remain stable. 'can reference on another sheet in a similar fashion to: 'testCellAddress=Worksheets("AnotherSheetName").Ra nge("B1") testCellAddress = Range("B1") ' .Value is implied singleColumnID = Range("B2") groupOneColumnID = Range("B3") groupTwoColumnID = Range("B4") groupThreeSourceID = Range("B5") groupThreeDestinationID = Range("B6") If Range(testCellAddress).Value = "Z" Then '1 col: copy Paste-Values to left 1 col Columns(singleColumnID).Select Selection.Copy Range(singleColumnID).Offset(0, -1).Select ' 1 column to the LEFT ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '22 col: (main, 21 col back up), COPY: Paste-Values to right 1 col Columns(groupOneColumnID).Select Selection.Copy Range(groupOneColumnID).Offset(0, 1).Select ' 1 column to the right ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '20 col: (10 sets of 2), COPY: Paste-Values to right 2 cols Columns(groupTwoColumnID).Select Selection.Copy Range(groupTwoColumnID).Offset(0, 2).Select ' 2 columns to the right ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False 'double col: (1 set of 2), COPY: Paste-Values to different section Columns(groupThreeSourceID).Select Selection.Copy Range(groupThreeDestinationID).Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False End If End Sub Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Target.Row < 130 Then Exit Sub If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'add "+" to blank spaces col A: If Not Intersect(Me.Range("a:a"), .Cells) Is Nothing Then Application.EnableEvents = False .Value = Replace(.Value, " ", "+") Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CK:CO"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination: With Me.Cells(.Row, "CF") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CW:CW"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination With Me.Cells(.Row, "CG") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If End With End Sub |
Change Reference to Columns in a Macro
Good to hear that the runtime error went away - I have replied to your email.
As for that runtime error, that is one of the reasons that most of us who program VBA in Excel recommend not using merged cells. Especially not in data areas. "Nastech" wrote: hi, scratch last note on error, had selected wrong cells in one of my fixed/ absolute cell locations. the script works great. thanks. for others: as using in/as reference cells: (for dynamic reference for script to changing column/cell locations) in cell: B1 gets: DN6 =SUBSTITUTE(SUBSTITUTE(CELL("address",$DN$6),"$"," "),"","") in cell: B2 gets: DU:DU, etc. for other columns =SUBSTITUTE(SUBSTITUTE(CELL("address",$DU2),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$DU2),"$",""),ROW(),"") script for moving data: (copy, paste-special-values); other script for putting dates in columns when entering data, etc. Option Explicit Private Sub CommandButton1_Click() Dim testCellAddress As String ' will hold "DN6" from B1 Dim singleColumnID As String ' will hold "DU:DU" from B2 Dim groupOneColumnID As String ' will hold "EE:EY" from B3 Dim groupTwoColumnID As String ' will hold "FE:FV" from B4 Dim groupThreeSourceID As String ' will hold "EC:ED" from B5 Dim groupThreeDestinationID As String ' will hold "FE:FF" from B6 'get the values from the active sheet. address must remain stable. 'can reference on another sheet in a similar fashion to: 'testCellAddress=Worksheets("AnotherSheetName").Ra nge("B1") testCellAddress = Range("B1") ' .Value is implied singleColumnID = Range("B2") groupOneColumnID = Range("B3") groupTwoColumnID = Range("B4") groupThreeSourceID = Range("B5") groupThreeDestinationID = Range("B6") If Range(testCellAddress).Value = "Z" Then '1 col: copy Paste-Values to left 1 col Columns(singleColumnID).Select Selection.Copy Range(singleColumnID).Offset(0, -1).Select ' 1 column to the LEFT ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '22 col: (main, 21 col back up), COPY: Paste-Values to right 1 col Columns(groupOneColumnID).Select Selection.Copy Range(groupOneColumnID).Offset(0, 1).Select ' 1 column to the right ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '20 col: (10 sets of 2), COPY: Paste-Values to right 2 cols Columns(groupTwoColumnID).Select Selection.Copy Range(groupTwoColumnID).Offset(0, 2).Select ' 2 columns to the right ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False 'double col: (1 set of 2), COPY: Paste-Values to different section Columns(groupThreeSourceID).Select Selection.Copy Range(groupThreeDestinationID).Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False End If End Sub Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Target.Row < 130 Then Exit Sub If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'add "+" to blank spaces col A: If Not Intersect(Me.Range("a:a"), .Cells) Is Nothing Then Application.EnableEvents = False .Value = Replace(.Value, " ", "+") Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CK:CO"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination: With Me.Cells(.Row, "CF") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CW:CW"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination With Me.Cells(.Row, "CG") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If End With End Sub |
All times are GMT +1. The time now is 06:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com