+ Reply to Thread
Results 1 to 11 of 11

Selecting Cell Relative to Contents of Cell

  1. #1
    Registered User
    Join Date
    02-22-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    11

    Selecting Cell Relative to Contents of Cell

    Hi everyone
    I am trying to make a "sales" page and this code is so i can add in sales for each day, but the problem i am having is every time i add in a sale it adds a row, as you can see, and so i can't just select specific cells to add the row to. So, my question is, can you select a cell relative to the contents of another cell? And if so how?

    Dim salday As Variant
    Dim salitm As Variant
    Dim salsld As Variant
    Dim salitn As Variant
    Dim STC As Range


    Set STC = Worksheets("Stock Sheet").Range("I6:M65")


    salday = frmUpdateSales.txtDay
    salitm = frmUpdateSales.txtItem
    salitn = frmUpdateSales.txtItemN
    salsld = frmUpdateSales.txtSold


    Sheets("Sales Sheet").Select


    If salday = "Monday" Then
    Rows("8:8").Select
    Selection.Insert Shift:=x1Down, CopyOrigin:=x1FormatFromLeftOrAbove
    Worksheets("Sales Sheet").Range("H8").Value = salitn
    Worksheets("Sales Sheet").Range("I8").Value = salsld
    Range("I8") = salsld
    Range("J8") = Application.VLookup(salitm, STC, 4, False)
    Range("K8").Formula = "=Sum(J8*I8)"
    Range("L8") = Application.VLookup(salitm, STC, 5, False)
    Range("M8").Formula = K8 - L8
    Last edited by Prometheus130; 02-26-2015 at 01:39 AM.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Selecting Cell Relative to Contents of Cell

    What type of event it is? Post the initial line of code


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Contributor
    Join Date
    07-29-2014
    Location
    Oz
    MS-Off Ver
    2010
    Posts
    142

    Re: Selecting Cell Relative to Contents of Cell

    So, my question is, can you select a cell relative to the contents of another cell?
    Yes. What are the criteria to test for and data ranges etc.

    It be very helpful if you could post a sample workbook with clear explanations of what you have, and what you want.

    Also, please use code tags when post vb code (it's the # button in the post editor window)

    Dan
    Don't forget to ☆ me if I helped you!

  4. #4
    Registered User
    Join Date
    02-22-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    11

    Re: Selecting Cell Relative to Contents of Cell

    ? This is the initial line of code

  5. #5
    Registered User
    Join Date
    02-22-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    11

    Re: Selecting Cell Relative to Contents of Cell

    Here is a workbook with my table in it

    I am hoping to lookup the day and select a cell two rows under it
    Attached Files Attached Files
    Last edited by Prometheus130; 02-23-2015 at 12:35 AM.

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Selecting Cell Relative to Contents of Cell

    No code exist in your attached workbook

  7. #7
    Registered User
    Join Date
    02-22-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    11

    Re: Selecting Cell Relative to Contents of Cell

    Ok, this is my original workbook just without the personal information

    Sorry about that, i didn't really understand what you wanted
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    07-29-2014
    Location
    Oz
    MS-Off Ver
    2010
    Posts
    142

    Re: Selecting Cell Relative to Contents of Cell

    I think this code does what you are after.

    For it to work, you must name the cell in your sales sheet that have the days of the week in them i.e. select H6, and in the name box type "Monday" (without the quotes). And so on for all the cells with days in them.

    I also think you need to handle errors in your code. If a user enters something that does not match anything in your stock sheet, or the days etc. you need to tell the code to do something about it i.e. message box or something to ask for correct info. Or even better, have combo boxes with lists of days, items and item numbers from your stock sheets, so they are selected from the list rather than typed.


    Please Login or Register  to view this content.
    Dan
    Last edited by Danerida; 02-23-2015 at 01:32 AM.

  9. #9
    Registered User
    Join Date
    02-22-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    11

    Re: Selecting Cell Relative to Contents of Cell

    Could you do me a massive favor and post a workbook with an example of what you mean
    (The one i gave you would be good)

  10. #10
    Registered User
    Join Date
    02-22-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    11

    Re: Selecting Cell Relative to Contents of Cell

    If anyone can supply an example of this code using the Book 2 given that would be really great and i will give Reputation to whoever does

  11. #11
    Forum Contributor
    Join Date
    07-29-2014
    Location
    Oz
    MS-Off Ver
    2010
    Posts
    142

    Re: Selecting Cell Relative to Contents of Cell

    Hi. Try the attachment. I have not added error handling or combo boxes as suggested above.
    Prometheus130_excelhelp.xlsm


    I understand that you have a need, however sending multiple private messages is not the best way to get people to help you. I have been extremely busy, and therefore not able to visit the forum. I also don't appreciate that you asked for help, I gave you steps to a solution, and you haven't bothered to try that solution. Instead you're asking me to do it for you.

+ 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. Relative Macro to Name a cell the contents of that cell.
    By HELPME13 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-15-2012, 02:54 AM
  2. Changing cell contents, format etc. without selecting
    By hstaubyn in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-23-2007, 03:38 AM
  3. VBA - Selecting a cell based on relative position vs cell #
    By jago_ML in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-08-2007, 03:04 AM
  4. Selecting a defined Name via a cell's contents
    By PCLIVE in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-14-2005, 02:05 PM
  5. Selecting a filepath based on cell contents
    By ceemo in forum Excel General
    Replies: 1
    Last Post: 06-25-2005, 09:05 AM

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