ActiveCell.Offset Question

[ash3154]

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

 

[smartin]

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,DEE")) Is Nothing Then
Cells(4, ActiveCell.Column).Copy
Range("BE199").Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
End Sub

 

[ash3154]

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]

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]

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 #).

 

[Don Guillett]

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.