Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
MACRO, syntax for move to different column
hi, I am using a keyboard shortcut for some copying tasks.
Offset can get to work, but does not suit purpose here. would like to use a named range, to maintain integrity of sheet, moving columns.. thanks Named Ranges represent columns (from & to): Dim M2 As String M2 = Range("M2") Dim N3 As String N3 = Range("N3") 'does not work in a Sub(); wish to change columns If Not Intersect(Me.Range(J3), .Cells) Is Nothing Then 'all With Me.Cells(.Row, J2).Select End With End If not working: how do I correct this, for the next item after: Selection(ActiveSheet.Row, N3).Select 'does not work Sub Paste2() 'alt-/ (slash) Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll Down:=190 'NEED TO MOVE TO A DIFFERENT COLUMN FROM HERE End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
MACRO, syntax for move to different column
Firstly where are you using this code? in a standard module or a worksheet module? if a worksheet module what event? Secondly i'd like to bet your code doesn't even compile as you haven't qualified some things like this line for instance Code: -------------------- If Not Intersect(Me.Range(J3), .Cells) Is Nothing Then -------------------- you haven't qualified .Cells, if there is more code to this then please supply all the code rather than snip out what you think we need to help you, this way we can give you an answer that will be helpful! MrDave;449742 Wrote: hi, I am using a keyboard shortcut for some copying tasks. Offset can get to work, but does not suit purpose here. would like to use a named range, to maintain integrity of sheet, moving columns.. thanks Named Ranges represent columns (from & to): Dim M2 As String M2 = Range("M2") Dim N3 As String N3 = Range("N3") 'does not work in a Sub(); wish to change columns If Not Intersect(Me.Range(J3), .Cells) Is Nothing Then 'all With Me.Cells(.Row, J2).Select End With End If not working: how do I correct this, for the next item after: Selection(ActiveSheet.Row, N3).Select 'does not work Sub Paste2() 'alt-/ (slash) Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll Down:=190 'NEED TO MOVE TO A DIFFERENT COLUMN FROM HERE End Sub -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=124510 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
MACRO, syntax for move to different column
hi, thanks, thats what I'm asking, don't know that much about macro's / vba..
I'm in a Module where use short cut keys made in toolbars, couple of items similar included here, but just need method of moving from 1 column to another (in modules), want to use a named range for dynamic movement of columns sake..... part of a copy-paste operation..... really just need syntax on moving from 1 col to another. I got the part about it doesn't work, came up on 20 attempts 1 day, 20 more today. examples working with: Sub Copy() 'alt-, (comma) wip: copy column 190 rows down from active cell, pending 'reduce all windows, restore main window, pause conflict with ie, etc. click button does not work Range(ActiveCell, ActiveCell.Offset(190, 0)).Copy End Sub Sub Paste1() 'alt-. (period) works: paste values to syma ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False End Sub Sub Paste2() 'alt-/ (slash) Dim M2 As String M2 = Range("M2") Dim N3 As String N3 = Range("N3") Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll Down:=190 Worksheet(.Row, N3).Select 'invalid or unqualified reference End Sub partial on other attempts: 'With Selection(.Row, N3).Select 'invalid or unqualified reference, been here before? 'End With 'With Selection 'With ActiveSheet(.Row, N3).Select 'object doesn't support this property or method 'End With 'End With 'If Not Intersect(ActiveSheet.Range(M2)) Is Nothing Then 'no 'With ActiveSheet(.Row, N3).Select 'End With 'End If 'If Not Intersect(ActiveSheet.Range(M2), .Cells) Is Nothing Then 'no 'With ActiveSheet.Cells(.Row, N3).Select 'End With 'End If 'ActiveSheet.Row, (N3).Select 'no 'Selection(ActiveSheet.Row, N3).Select 'no 'Selection(ActiveCell.Row, N3).Select 'did something, jumped 2k rows down though 'With Ativesheet(N3).Select ' 'With Selection(N3).Select 'type mismatch 'With Selection(.Row, N3).Select 'no 'With Me.Cells(.Row, N3).Select 'no 'Range ActiveRow(.Row, N3).Select 'invalid or unqualified reference 'Range ActiveRow(.Row, N3).Select 'invalid or unqualified reference 'ActiveRow(.Row, N3).Select 'invalid or unqualified reference 'ActiveRow(N3).Select 'sub or function not defined 'ActiveSheet.Row(N3).Select 'no 'Range(ActiveSheet, ActiveSheet.Row, N3).Select 'no 'Range(ActiveSheet.Row, N3).Select 'not work 'ActiveSheet(.Row, N3).Select 'not work 'Range(ActiveSheet, ActiveCell.Offset(190, 0)).Copy 'End With 'If Not Intersect(ActiveSheet.Range(M2)) Is Nothing Then 'wrong ' With ActiveSheet(.Row, N3).Select ' '.Offset(0, 0).Select ' End With 'End If 'If Not Intersect(Me.Range(M2), .Cells) Is Nothing Then 'original ' With Me.Cells(.Row, N3).Select ' '.Offset(0, 0).Select ' End With 'End If 'With Me.Cells(.Row, N3).Select 'Range(ActiveCell, ActiveCell.Offset(190, 0)).Copy End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
MACRO, syntax for move to different column
Where you have an unqualified reference its because you are using .Rows or .Cells, unfortunately unless you state the object before it Excel doesn't know which sheet or range you mean, so you would need to take a look at your with statements, for instance you can't do this Code: -------------------- 'With Selection(.Row, N3).Select -------------------- a normal With statement would be something like Code: -------------------- With Sheets("Sheet1") .Range("A1").Interior.ColorIndex = 3 .Cells(2, 2).Value = "Hello" .Rows("3:3").Interior.ColorIndex = 6 End With -------------------- Unfortunately even with all that code you supplied i still can't get a grasp on what you want. For further help with it why not join our forums (shown in the link below) it's completely free, if you do join you will have the opportunity to add attachments to your posts so you can add workbooks to better illustrate your problems and get help directly with them. It will make life easier for both you and i!. :)MrDave;449964 Wrote: hi, thanks, thats what I'm asking, don't know that much about macro's / vba.. I'm in a Module where use short cut keys made in toolbars, couple of items similar included here, but just need method of moving from 1 column to another (in modules), want to use a named range for dynamic movement of columns sake..... part of a copy-paste operation..... really just need syntax on moving from 1 col to another. I got the part about it doesn't work, came up on 20 attempts 1 day, 20 more today. examples working with: Code: -------------------- Sub Copy() 'alt-, (comma) wip: copy column 190 rows down from active cell, pending 'reduce all windows, restore main window, pause conflict with ie, etc. click button does not work Range(ActiveCell, ActiveCell.Offset(190, 0)).Copy End Sub Sub Paste1() 'alt-. (period) works: paste values to syma ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False End Sub Sub Paste2() 'alt-/ (slash) Dim M2 As String M2 = Range("M2") Dim N3 As String N3 = Range("N3") Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll Down:=190 Worksheet(.Row, N3).Select 'invalid or unqualified reference End Sub partial on other attempts: 'With Selection(.Row, N3).Select 'invalid or unqualified reference, been here before? 'End With 'With Selection 'With ActiveSheet(.Row, N3).Select 'object doesn't support this property or method 'End With 'End With 'If Not Intersect(ActiveSheet.Range(M2)) Is Nothing Then 'no 'With ActiveSheet(.Row, N3).Select 'End With 'End If 'If Not Intersect(ActiveSheet.Range(M2), .Cells) Is Nothing Then 'no 'With ActiveSheet.Cells(.Row, N3).Select 'End With 'End If 'ActiveSheet.Row, (N3).Select 'no 'Selection(ActiveSheet.Row, N3).Select 'no 'Selection(ActiveCell.Row, N3).Select 'did something, jumped 2k rows down though 'With Ativesheet(N3).Select ' 'With Selection(N3).Select 'type mismatch 'With Selection(.Row, N3).Select 'no 'With Me.Cells(.Row, N3).Select 'no 'Range ActiveRow(.Row, N3).Select 'invalid or unqualified reference 'Range ActiveRow(.Row, N3).Select 'invalid or unqualified reference 'ActiveRow(.Row, N3).Select 'invalid or unqualified reference 'ActiveRow(N3).Select 'sub or function not defined 'ActiveSheet.Row(N3).Select 'no 'Range(ActiveSheet, ActiveSheet.Row, N3).Select 'no 'Range(ActiveSheet.Row, N3).Select 'not work 'ActiveSheet(.Row, N3).Select 'not work 'Range(ActiveSheet, ActiveCell.Offset(190, 0)).Copy 'End With 'If Not Intersect(ActiveSheet.Range(M2)) Is Nothing Then 'wrong ' With ActiveSheet(.Row, N3).Select ' '.Offset(0, 0).Select ' End With 'End If 'If Not Intersect(Me.Range(M2), .Cells) Is Nothing Then 'original ' With Me.Cells(.Row, N3).Select ' '.Offset(0, 0).Select ' End With 'End If 'With Me.Cells(.Row, N3).Select 'Range(ActiveCell, ActiveCell.Offset(190, 0)).Copy End Sub -------------------- -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=124510 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to find and move column | Excel Discussion (Misc queries) | |||
macro to move to minimum value in column of data | Excel Discussion (Misc queries) | |||
Move Column within Sheet with VB Macro | Excel Discussion (Misc queries) | |||
How do I record a macro to move down and over to specific column | Excel Discussion (Misc queries) | |||
Macro to move to next column | Excel Discussion (Misc queries) |