Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I am struggling to compile some code that is technically beyond my capabilities, so would really appreciate some help.

    I have added comments to the code below that hopefully explain what I am attempting to do.

    Code:
    Sub Test()
    
    Dim amt As Worksheet, ath As Worksheet, aat As Worksheet
    Dim r As Long, s As Long
    Dim x As Range, y As Range, z As Range
    Dim j As String
    
    	Set amt = Worksheets("Admin MGR History")
    	Set ath = Worksheets("Admin TFR History")
    	Set aat = Worksheets("Admin Action TFR'S")
    	
    	r = aat.Cells(Rows.Count, 3).End(xlUp).Row
    	
    	'irrelevant code removed
    		
    	j = aat.Range("E5")
    	
    	'Now I need to capture the row number that j is on in amt, column A, rows 2 : endxlup
    	'This becomes the destination row
    
    	Set x = aat.Range("K35:K" & r)
    	For Each y In x
    	
    	'Copy y.offset(0,13) and paste as special values to the first blank cell in the range of >
    		'either BZ, CC, CF, CI or CL on the destination row
    	'Then copy y.offset(0,17) and paste as special values to the first blank cell in the range of >
    		'either CA, CD, CG, CJ or CM on the destination row
    		
    	'Find y on the destination row (which will be within the range of columns E:P) and >
    		'replace it with y.offset(0, -8)
    		
    	Next
    
    'irrelevant code removed
    
    End Sub
    Thanks

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It is appreciated that:
    • the material on which you're working may be confidential
    • you don't want to burden us with excessive detail
    Nonetheless, it could be helpful if you could post a sample workbook - as (for me at least) a wider perspective on your problem could be helpful.

    HTH
    Gre

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='unkamunka' post='796243' date='04-Oct-2009 08:47']It is appreciated that:
    • the material on which you're working may be confidential
    • you don't want to burden us with excessive detail
    Nonetheless, it could be helpful if you could post a sample workbook - as (for me at least) a wider perspective on your problem could be helpful.

    HTH[/quote]

    Ok, Thanks. Not majorly confidential, but there is a lot of excess in the file. I am trying to sort out a stripped down copyof the 16mb file, after removing the vast majority (about 80%), the file size is still 13mb.

    I'll keep trying.....

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here goes....

    I have had to remove the vast majority of the formula's due to the fact that they were looking at one sheet that is 11mb and I could not reduce it. That sheet contains a web query.

    Hopefully this will be enough to give the general idea.

    Thanks

    [attachment=85803:lounge_t...__Copy_2.xls]
    Attached Files Attached Files

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='VegasNath' post='796223' date='03-Oct-2009 16:29']'Now I need to capture the row number that j is on in amt, column A, rows 2 : endxlup
    'This becomes the destination row[/quote]

    Hi Nathan

    Can you further explain "amt, column A, rows 2 : endxlup"
    Regards
    Don

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Don,

    This has moved on somewhat (Very slowly) and I am (slightly) past that point.

    Hopefully the attached explains where I am now stuck.

    Thanks for any help.

    [attachment=85810:Lounge_Code.txt]
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Range(p, q) is the rectangular range with p and q as opposite corners. If you want to copy two non-adjacent cells, you must copy each of them individually.

  8. #8
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    So a non contiguous range cannot be copied? I was not aware of that.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You *can* copy a non-contiguous range, but not with the syntax you tried.
    If you have two ranges p and q that represent non-adjacent cells, you'd have to use

    Union(p, q).Copy Destination:=r

    But even if p and q are non-adjacent, they will be copied into adjacent cells. There is no way to copy for example A1 and E1 into A5 and E5 in one go.

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans, I've only just seen your post, I was beavering away and am now nigh on complete.

    I am struggling with the very last little bit and would appreciate any help that loungers can offer.

    Updated: [attachment=85811:Lounge_Code.txt]
    Attached Files Attached Files

  11. #11
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code:
    Set w = amt.Range("E" & p & ":P" & p).Find(What:=y.Value, LookIn:=xlValues, LookAt:=xlWhole)
    	w.Value = y.Offset(0, -8)
    Got there eventually.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •