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

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

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

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