ActiveCell.Offset Question
 

Hi,

Spreadsheet with many columns:
In All column, Row4 I have dates:

What I like to do is:
"IF" I am in Column y "Any row" I would like to go up to Y4 and copy that
value down to another cell .
"IF" I am in Column de "Any row" I would like to go up to de4 and copy that
value down to another cell .

I wrote this thinking it may work, but I keep getting mismatch Type:
Sub Look4DATE()


Dim mycell As Range
Set mycell = ActiveCell.Offset(0, 0)
Dim myrange As Range
Set myrange = Range(ActiveCell.Offset(0, 0), mycell)

mycell.Select
ActiveCell.Offset(ActiveCell.Column & ActiveCell.Row & ":" &
ActiveCell.Column & "4").Select
Selection.Copy
Range("bE199").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


End Sub

ActiveCell.Offset Question
 

ash3154 wrote:
Hi,

Spreadsheet with many columns:
In All column, Row4 I have dates:

What I like to do is:
"IF" I am in Column y "Any row" I would like to go up to Y4 and copy that
value down to another cell .
"IF" I am in Column de "Any row" I would like to go up to de4 and copy that
value down to another cell .

I wrote this thinking it may work, but I keep getting mismatch Type:
Sub Look4DATE()


Dim mycell As Range
Set mycell = ActiveCell.Offset(0, 0)
Dim myrange As Range
Set myrange = Range(ActiveCell.Offset(0, 0), mycell)

mycell.Select
ActiveCell.Offset(ActiveCell.Column & ActiveCell.Row & ":" &
ActiveCell.Column & "4").Select
Selection.Copy
Range("bE199").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


End Sub

Maybe this:

Sub Look4DATE()
If Not Application.Intersect(Range(ActiveCell.Address), _
Range("Y:Y,DE:DE")) Is Nothing Then
Cells(4, ActiveCell.Column).Copy
Range("BE199").Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
End Sub

ActiveCell.Offset Question
 

Thks for a quick response: Unfortunately, I have more than 2 columns, I have
over 35 columns, which I need to get the value of.

"smartin" wrote:

ash3154 wrote:
Hi,

Spreadsheet with many columns:
In All column, Row4 I have dates:

What I like to do is:
"IF" I am in Column y "Any row" I would like to go up to Y4 and copy that
value down to another cell .
"IF" I am in Column de "Any row" I would like to go up to de4 and copy that
value down to another cell .

I wrote this thinking it may work, but I keep getting mismatch Type:
Sub Look4DATE()


Dim mycell As Range
Set mycell = ActiveCell.Offset(0, 0)
Dim myrange As Range
Set myrange = Range(ActiveCell.Offset(0, 0), mycell)

mycell.Select
ActiveCell.Offset(ActiveCell.Column & ActiveCell.Row & ":" &
ActiveCell.Column & "4").Select
Selection.Copy
Range("bE199").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


End Sub

Maybe this:

Sub Look4DATE()
If Not Application.Intersect(Range(ActiveCell.Address), _
Range("Y:Y,DE:DE")) Is Nothing Then
Cells(4, ActiveCell.Column).Copy
Range("BE199").Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
End Sub

ActiveCell.Offset Question
 

Ok, I'm guessing the columns are not contiguous. That would be too easy.
Is there any sort of pattern we can capitalize on (every other column,
every third, etc.) Another option, is to enumerate the columns in

Range("Y:Y,AA:AA,CC:CC,...")

Or find a way to automate the same.

ash3154 wrote:
Thks for a quick response: Unfortunately, I have more than 2 columns, I have
over 35 columns, which I need to get the value of.

"smartin" wrote:

ash3154 wrote:
Hi,

Spreadsheet with many columns:
In All column, Row4 I have dates:

What I like to do is:
"IF" I am in Column y "Any row" I would like to go up to Y4 and copy that
value down to another cell .
"IF" I am in Column de "Any row" I would like to go up to de4 and copy that
value down to another cell .

I wrote this thinking it may work, but I keep getting mismatch Type:
Sub Look4DATE()


Dim mycell As Range
Set mycell = ActiveCell.Offset(0, 0)
Dim myrange As Range
Set myrange = Range(ActiveCell.Offset(0, 0), mycell)

mycell.Select
ActiveCell.Offset(ActiveCell.Column & ActiveCell.Row & ":" &
ActiveCell.Column & "4").Select
Selection.Copy
Range("bE199").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


End Sub
Maybe this:

Sub Look4DATE()
If Not Application.Intersect(Range(ActiveCell.Address), _
Range("Y:Y,DE:DE")) Is Nothing Then
Cells(4, ActiveCell.Column).Copy
Range("BE199").Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
End Sub

ActiveCell.Offset Question
 

Yes after your previous script that is what I have done.... its every other
column. Thanks for your help; I will mark this as Resolved.

I am running into another issue: Just above my active cell, I have a email
address cell, if that is blank, grab the number which is 2 cells below my
active cell and call the number which is 10 rows above my active cell.
(basically I need to inform whoever is clicking on that macro button to call
that person with the ticket #).



"smartin" wrote:

Ok, I'm guessing the columns are not contiguous. That would be too easy.
Is there any sort of pattern we can capitalize on (every other column,
every third, etc.) Another option, is to enumerate the columns in

Range("Y:Y,AA:AA,CC:CC,...")

Or find a way to automate the same.

ash3154 wrote:
Thks for a quick response: Unfortunately, I have more than 2 columns, I have
over 35 columns, which I need to get the value of.

"smartin" wrote:

ash3154 wrote:
Hi,

Spreadsheet with many columns:
In All column, Row4 I have dates:

What I like to do is:
"IF" I am in Column y "Any row" I would like to go up to Y4 and copy that
value down to another cell .
"IF" I am in Column de "Any row" I would like to go up to de4 and copy that
value down to another cell .

I wrote this thinking it may work, but I keep getting mismatch Type:
Sub Look4DATE()


Dim mycell As Range
Set mycell = ActiveCell.Offset(0, 0)
Dim myrange As Range
Set myrange = Range(ActiveCell.Offset(0, 0), mycell)

mycell.Select
ActiveCell.Offset(ActiveCell.Column & ActiveCell.Row & ":" &
ActiveCell.Column & "4").Select
Selection.Copy
Range("bE199").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


End Sub
Maybe this:

Sub Look4DATE()
If Not Application.Intersect(Range(ActiveCell.Address), _
Range("Y:Y,DE:DE")) Is Nothing Then
Cells(4, ActiveCell.Column).Copy
Range("BE199").Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
End Sub

ActiveCell.Offset Question
 

If desierd, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You send a clear explanation of what you want
3. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ash3154" wrote in message
...
Yes after your previous script that is what I have done.... its every
other
column. Thanks for your help; I will mark this as Resolved.

I am running into another issue: Just above my active cell, I have a
email
address cell, if that is blank, grab the number which is 2 cells below my
active cell and call the number which is 10 rows above my active cell.
(basically I need to inform whoever is clicking on that macro button to
call
that person with the ticket #).



"smartin" wrote:

Ok, I'm guessing the columns are not contiguous. That would be too easy.
Is there any sort of pattern we can capitalize on (every other column,
every third, etc.) Another option, is to enumerate the columns in

Range("Y:Y,AA:AA,CC:CC,...")

Or find a way to automate the same.

ash3154 wrote:
Thks for a quick response: Unfortunately, I have more than 2 columns,
I have
over 35 columns, which I need to get the value of.

"smartin" wrote:

ash3154 wrote:
Hi,

Spreadsheet with many columns:
In All column, Row4 I have dates:

What I like to do is:
"IF" I am in Column y "Any row" I would like to go up to Y4 and copy
that
value down to another cell .
"IF" I am in Column de "Any row" I would like to go up to de4 and
copy that
value down to another cell .

I wrote this thinking it may work, but I keep getting mismatch Type:
Sub Look4DATE()


Dim mycell As Range
Set mycell = ActiveCell.Offset(0, 0)
Dim myrange As Range
Set myrange = Range(ActiveCell.Offset(0, 0), mycell)

mycell.Select
ActiveCell.Offset(ActiveCell.Column & ActiveCell.Row & ":" &
ActiveCell.Column & "4").Select
Selection.Copy
Range("bE199").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


End Sub
Maybe this:

Sub Look4DATE()
If Not Application.Intersect(Range(ActiveCell.Address), _
Range("Y:Y,DE:DE")) Is Nothing Then
Cells(4, ActiveCell.Column).Copy
Range("BE199").Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
End Sub