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





0
ash3154 (6)
9/12/2009 12:48:02 AM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
556 Views

Similar Articles

[PageSpeed] 33

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
0
smartin108 (170)
9/12/2009 1:47:53 AM
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
> 
0
ash3154 (6)
9/12/2009 2:53:01 AM
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
>>
0
smartin108 (170)
9/12/2009 3:13:25 AM
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
> >>
> 
0
ash3154 (6)
9/12/2009 3:47:01 AM
      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
dguillett1@austin.rr.com
"ash3154" <ash3154@discussions.microsoft.com> wrote in message 
news:B1B64490-B704-42FB-94E2-625919DFA521@microsoft.com...
> 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
>> >>
>> 

0
dguillett1 (2487)
9/12/2009 12:46:11 PM
Reply:

Similar Artilces: