MACRO, syntax for move to different column

[MrDave]

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]

Firstly where are you using this code? in a standard module or
worksheet module? if a worksheet module what event

Secondly i'd like to bet your code doesn't even compile as you haven'
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 pleas
supply all the code rather than snip out what you think we need to hel
you, this way we can give you an answer that will be helpful

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, movin
columns.
thank

Named Ranges represent columns (from & to)

Dim M2 As Strin
M2 = Range("M2"
Dim N3 As Strin
N3 = Range("N3"

'does not work in a Sub(); wish to change column
If Not Intersect(Me.Range(J3), .Cells) Is Nothing Then 'al
With Me.Cells(.Row, J2).Selec
End Wit
End I



not working: how do I correct this, for the next item after
Selection(ActiveSheet.Row, N3).Select 'does not wor


Sub Paste2() 'alt-/ (slash
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks
:=False, Transpose:=Fals
ActiveWindow.SmallScroll Down:=19

'NEED TO MOVE TO A DIFFERENT COLUMN FROM HER

End Su

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[MrDave]

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

 

[Simon Lloyd]

Where you have an unqualified reference its because you are using .Row
or .Cells, unfortunately unless you state the object before it Exce
doesn't know which sheet or range you mean, so you would need to take
look at your with statements, for instance you can't do thi
Code
-------------------
'With Selection(.Row, N3).Selec
-------------------
a normal With statement would be something lik
Code
-------------------
With Sheets("Sheet1"
.Range("A1").Interior.ColorIndex =
.Cells(2, 2).Value = "Hello
.Rows("3:3").Interior.ColorIndex =
End Wit

-------------------
Unfortunately even with all that code you supplied i still can't get
grasp on what you want

For further help with it why not join our forums (shown in the lin
below) it's completely free, if you do join you will have th
opportunity to add attachments to your posts so you can add workbooks t
better illustrate your problems and get help directly with them. I

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 o
item
similar included here, but just need method of moving from 1 column t
another (in modules), want to use a named range for dynamic movement o
columns sake..... part of a copy-paste operation..... really just nee
syntax on moving from 1 col to another

I got the part about it doesn't work, came up on 20 attempts 1 day, 2
mor
today
examples working with

Code
-------------------
Sub Copy() 'alt-, (comma) wip: copy column 190 rows down from activ
cell, pendin
'reduce all windows, restore main window, pause conflict with ie, etc
click button does not wor
Range(ActiveCell, ActiveCell.Offset(190, 0)).Cop
End Su

Sub Paste1() 'alt-. (period) works: paste values to sym
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False,
IconFileName:=Fals
End Su

Sub Paste2() 'alt-/ (slash
Dim M2 As Strin
M2 = Range("M2"
Dim N3 As Strin
N3 = Range("N3"

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks
:=False, Transpose:=Fals
ActiveWindow.SmallScroll Down:=19


Worksheet(.Row, N3).Select 'invalid or unqualified referenc

End Su


partial on other attempts

'With Selection(.Row, N3).Select 'invalid or unqualified reference, bee
here before
'End Wit

'With Selectio
'With ActiveSheet(.Row, N3).Select 'object doesn't support thi
property or metho
'End Wit
'End Wit

'If Not Intersect(ActiveSheet.Range(M2)) Is Nothing Then 'n
'With ActiveSheet(.Row, N3).Selec
'End Wit
'End I

'If Not Intersect(ActiveSheet.Range(M2), .Cells) Is Nothing Then 'n
'With ActiveSheet.Cells(.Row, N3).Selec
'End Wit
'End I

'ActiveSheet.Row, (N3).Select 'n
'Selection(ActiveSheet.Row, N3).Select 'n
'Selection(ActiveCell.Row, N3).Select 'did something, jumped 2k row
down thoug

'With Ativesheet(N3).Select
'With Selection(N3).Select 'type mismatc
'With Selection(.Row, N3).Select 'n
'With Me.Cells(.Row, N3).Select 'n

'Range ActiveRow(.Row, N3).Select 'invalid or unqualified referenc

'Range ActiveRow(.Row, N3).Select 'invalid or unqualified referenc
'ActiveRow(.Row, N3).Select 'invalid or unqualified referenc
'ActiveRow(N3).Select 'sub or function not define

'ActiveSheet.Row(N3).Select 'n
'Range(ActiveSheet, ActiveSheet.Row, N3).Select 'n
'Range(ActiveSheet.Row, N3).Select 'not wor
'ActiveSheet(.Row, N3).Select 'not wor

'Range(ActiveSheet, ActiveCell.Offset(190, 0)).Cop
'End Wit

'If Not Intersect(ActiveSheet.Range(M2)) Is Nothing Then 'wron
' With ActiveSheet(.Row, N3).Selec
' '.Offset(0, 0).Selec
' End Wit
'End I

'If Not Intersect(Me.Range(M2), .Cells) Is Nothing Then 'origina
' 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)