+ Reply to Thread
Results 1 to 11 of 11

Find Cell in different worksheet, Offset and Paste in multiple cells

  1. #1
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Find Cell in different worksheet, Offset and Paste in multiple cells

    Hi All,

    My Problem:

    I have a Workbook with 2 Worksheets: "Ausgleich" and "Ausgleichtabelle".

    ------------------------------------------------------------------------------------------------------------------------------------------

    In worksheet "Ausgleich" i have 3 Activex Textboxes and 1 Activex ComboBox: "TextBox1", "TextBox5", "TextBox6" and "ComboBox1" and 1 CommandButton1.

    TextBox1 = Username (JeRi), TextBox5 = Tätigkeit, TextBox6 = Datum, ComboBox1 = 0,5 or 1 (TextBox1 is Autofilled by Login Username, Other 3 are User Filled)

    TextBoxes.PNG

    -------------------------------------------------------------------------------------------------------------------------------------------

    In Worksheet "Ausgleichtabelle" i have Data for each User in 6 columns, Data range A15:T200: example:

    cells.PNG

    --------------------------------------------------------------------------------------------------------------------------------------------

    The code i need is as follows:

    User fills out the 2 Textboxes and ComboBox in "Ausgleich" Worksheet.

    Clicks CommandButton1:
    Code looks in Sheets("Ausgleichtabelle").Range("A15:T200") for TextBox1.Value,
    finds next empty cell below this cell,
    Offsets by 3 columns,
    Pastes the Value of "TextBox5", "TextBox6", "ComboBox1" in the next 3 columns starting from the offset.

    Hope this makes sense.

    Any Help would be greatly appreciated.

    Regards

    Jeremy
    Last edited by JRidge; 07-30-2014 at 05:27 AM.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Find Cell in different worksheet, Offset and Paste in multiple cells

    Try this. Put the code in the worksheet's code module.

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: Find Cell in different worksheet, Offset and Paste in multiple cells

    Hi Alphafrog,

    Wow ....

    One slight amendment that i didn't foresee

    When it finds the username in "Ausgleichtabelle",
    From that cell it needs to first go 1 column right, Then 3 rows down before starting the free cell search, then paste the values..

    Sorry i missed i had empty cells between the username and the headings...

    Regards

    jeremy

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Find Cell in different worksheet, Offset and Paste in multiple cells

    You're welcome.

    Could you attach an example file with a specific example of a username and where the next values are suppose to go.? The pictures don't help that much and it looks like you're using merged cells which can affect how things work.

  5. #5
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: Find Cell in different worksheet, Offset and Paste in multiple cells

    I have already removed the merged cells in the Master document, i thought they would cause Trouble. Thats why there are now Empty cells between the username and where to Start the searching for the next Empty cell.

    Will try and and Upload a sample, just got home and need to Start my work Laptop up

  6. #6
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: Find Cell in different worksheet, Offset and Paste in multiple cells

    Ok this is the 2 worksheets in question.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: Find Cell in different worksheet, Offset and Paste in multiple cells

    Hey Alphafrog,

    On the Ausgleich Worksheet you will see 2 sets of text boxes with a commandbutton.

    The single text box to the right is where the Username is (Filled automatically in Master Document from Login Username)

    The first set of 3 boxes and command button (Left ones) enter the data on the right side of the line under the username in the Ausgleichtabelle


    The second set of 3 boxes and command button (Right ones) enter the data on the left side of the line.

    Hope this makes sense.

    Regards

    Jeremy

  8. #8
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Find Cell in different worksheet, Offset and Paste in multiple cells

    Change this...
    Please Login or Register  to view this content.
    To this...
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: Find Cell in different worksheet, Offset and Paste in multiple cells

    wow, Works great.

    Can you explain this offset bit so i can adapt it for my other functions:

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: Find Cell in different worksheet, Offset and Paste in multiple cells

    Got it to work and offset properly for the other Data input

    Please Login or Register  to view this content.

  11. #11
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Find Cell in different worksheet, Offset and Paste in multiple cells

    Well done.

    FYI: highlight the term Offset in the code and press F1 for help on that function.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 02-04-2014, 01:57 PM
  2. Use Userform Textbox to find worksheet cell value and offset.
    By snuggie in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-01-2013, 11:58 AM
  3. Loop to find a cell, then copy offset paste
    By mr.alexander in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-29-2013, 08:48 AM
  4. find value in multiple sheets, copy offset and paste to summary sheet
    By Pholic in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-02-2011, 12:23 PM
  5. Replies: 1
    Last Post: 11-13-2010, 06:33 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1