1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Variable offset?

Discussion in 'Business Applications' started by harrydancer, Feb 5, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. harrydancer

    harrydancer Thread Starter

    Joined:
    Feb 5, 2011
    Messages:
    4
    Hi, I couldnt find an answer to my problem from google searching.

    Basically I want to transfer data from one workbook to another using the find functions of find, copy, switch workbooks, find, offset, paste, which will run on a couple of hundred names. But I want the offset to change depending on a value I put into one cell in the sheet, i.e. if B2=2 it offsets by 2 columns, if it =3 it offsets by three columns etc, is this possible? I tried putting offset.(FN2,0) and assigning FN2 to equal B2 but it failed.

    And pointers would be welcome, thanks.
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Welcome to the board.

    Both workbooks are open simultaneously?

    Edit: define "failed". You want to offset by columns -- the offset "syntax" is offset(rows,columns) so with offset.(FN2,0) you're actually offsetting by FN2 rows.
     
  3. harrydancer

    harrydancer Thread Starter

    Joined:
    Feb 5, 2011
    Messages:
    4
    Thanks Bomb, yeah the workbooks are open at the same time.

    When I run a macro with ActiveCell.Offset(0, FN3).Paste it says "object doesnt support this property or method". I was just hoping I could change one cell in my sheet that contains the macro to make all the offset functions in the macro change rather than change all the offsets each time.
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Well, you don't even need the "copy - switch - find - offset - paste" routine.

    With the "source" workbook active and the "target" workbook ("Book2") open,

    Range("A1").Copy Workbooks("Book2.xls").Sheets("Sheet1").Range("A1").Offset(, Range("B2"))

    is a lot "cleaner".
     
  5. harrydancer

    harrydancer Thread Starter

    Joined:
    Feb 5, 2011
    Messages:
    4
    You lost me Bomb, you may need to give up on me, I need to hold onto my rudimentary copy and pastes for now until I get more macro savvy, my code is below, its just the last bit that it doesnt like. I havent ignored your code I just dont know how to begin to use it :(

    FN1 = Range("C2")
    FN2 = Range("C5")
    FN3 = Range("D3")
    FN4 = Range("B5")

    Range("B16:C16").Select
    Selection.Copy
    Range("B5").Select
    ActiveSheet.Paste
    Windows(FN1).Activate
    Sheets("CONDENSED_CONSOLIDATED_STATEME").Select
    Cells.Find(What:=FN4, After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Copy
    Windows("macro sheet.xls").Activate
    ActiveCell.Offset(FN2, FN3).Paste
     
  6. harrydancer

    harrydancer Thread Starter

    Joined:
    Feb 5, 2011
    Messages:
    4
    Sorry I see what I was doing wrong now, I thought it was a more technical problem than it actually was! was just missing some brackets
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/979049

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice