+ Reply to Thread
Results 1 to 25 of 25

How to fix a vba code that returns a cell reference

  1. #1
    Forum Contributor
    Join Date
    08-14-2014
    Location
    US
    MS-Off Ver
    2013
    Posts
    135

    How to fix a vba code that returns a cell reference

    How this works is if data is input in the next available cell in a column this code sees what cell it was and in another of area of the sheet input the cell location like =YXX the XX being the cell number. and Y being the Column

    using the nextrw from this line
    Please Login or Register  to view this content.
    I was able to come up with this
    Please Login or Register  to view this content.
    Which does work but need to edit it so if the main cell is blank or has a -this would return a blank I tried
    Please Login or Register  to view this content.
    kind of a hail mary

    So went back to what worked before but the code looks ridiculous and wonder can someone please fix this so it looks like a formula that is easier to edit. Dont laugh too hard but here is what I came up with for a simple formula that should come out looking like this sampel =IF(YXX<"",YXX,"") the YXX are what the script finds using the NextRw

    Please Login or Register  to view this content.
    It does work just hard to read and edit.

    Copy of the stripped down workbook
    Last edited by Mr_Bill; 09-21-2014 at 12:56 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,771

    Re: How to fix a vba code that returns a cell reference

    Can we see the code in a sample workbook please?
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    08-14-2014
    Location
    US
    MS-Off Ver
    2013
    Posts
    135

    Re: How to fix a vba code that returns a cell reference

    Here is a copy of the stripped down version

    Workbook

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,771

    Re: How to fix a vba code that returns a cell reference

    If I'm honest, I'm not 100% sure what you are trying to do. But maybe this will point you in the right direction:

    Please Login or Register  to view this content.

    Regards, TMS

  5. #5
    Forum Contributor
    Join Date
    08-14-2014
    Location
    US
    MS-Off Ver
    2013
    Posts
    135

    Re: How to fix a vba code that returns a cell reference

    Its very hard to explain so I think that is why im confusing people.
    The userform will input the data and data only in two place how it works now. Problem with this is if I edit the main location range 7-190 I have to edit the second location to match. I need the second area as it performs the calculations. This second area is after row 202

    So for example if the next available area for the userform to input data is row 7 than (txtPointsReceived) is input into D7 so would have a value of say 97
    Now around row 200 there are 5 columns all with a header that match the assignment type picked in the userform. The (txtPointsReceived) get put into the corresponding column.
    I want to change that part instead of it adding a value say 97 I want it to install in this example =IF(D7<"",D7,"") the D7 is the part I am having an issue with I cant get the script to echo the cell that the first value was put in.
    This line is the one I am having an issue with. If try to input the formula here I get errors. I found if I put "=" & Chr(68) & NextRw it works but that is an example D7 so in order to get the rest of the formula to work with no vba error I had to use a ton of Chr for the parenthesis and commas letters etc.
    Please Login or Register  to view this content.
    ' *********************************************************************
    ' Data input for active gradebook weighted key
    ' *********************************************************************
    Below this are the rows 200+ the Key I call it which the problem area. In your sample you edited the area above it.



    In a nut shell I would like this line of code to go from this
    Please Login or Register  to view this content.
    To something like this
    Please Login or Register  to view this content.
    No clue what to put for the ?????

    If I try something like
    Please Login or Register  to view this content.
    I get "Run-Time Error 1004: Application-defined or object-defined error" with

    without the parenthesis I get a run time error 13 type mismatch
    Last edited by Mr_Bill; 09-21-2014 at 04:09 PM.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,771

    Re: How to fix a vba code that returns a cell reference

    OK, now I understand what you're trying to do. What I don't really understand is why you're trying to do it by inserting formulae.

    You're driving it with code so you may as well just work out what you need to store and where you need to store it and put it there.

    I've commented out all the lines of code you use to put a formula in place and added this code at the end of the subroutine.

    Please Login or Register  to view this content.

    See the updated example


    Regards, TMS
    Attached Files Attached Files

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,771

    Re: How to fix a vba code that returns a cell reference

    Sorry ... forum seems to be dumping on the upload. I'll try again now. If that fails, maybe tomorrow.

    Regards, TMS
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    08-14-2014
    Location
    US
    MS-Off Ver
    2013
    Posts
    135

    Re: How to fix a vba code that returns a cell reference

    Whoa that is a super condensed version I love it. Still have the original issue of updating them both. The number that is put into the upper area is cosmetic only of seeing records at a glance. The bottom one is part of a weighted calculation that I cant do without the key. At least I haven't figured out a way. So when I edit the top number the bottom one should also change this is why I was trying to add the formula to echo the top score. This is a generated page and there will be more so trying to keep all data for this page on this page.

    In the other version I edited that line in a five places
    Please Login or Register  to view this content.
    and it does work. Now to figure out how to get it to work with your super condensed version

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,771

    Re: How to fix a vba code that returns a cell reference

    Maybe, this:

    Please Login or Register  to view this content.

    Becomes this:

    Please Login or Register  to view this content.

    Regards, TMS

  10. #10
    Forum Contributor
    Join Date
    08-14-2014
    Location
    US
    MS-Off Ver
    2013
    Posts
    135

    Re: How to fix a vba code that returns a cell reference

    Awesome, One final question this one works great with columns A-E or 1-5 How would I modify it to work with G-K or 7-11. I have 3 sets of this for three different userforms one group is A:E with D being the txtPointsReceived column G:K with J being the txtPointsReceived

    I should see the pattern after those two to be able to do the third one

    Thank you very much this is spectacular. Afraid what people would think if they saw all the code if you made this part that small



    You must spread some Reputation around before giving it to TMS again. I tried

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,771

    Re: How to fix a vba code that returns a cell reference

    Sorry, too late for me to do it right now ... but later. Don't think it wii be too difficult.

    Regards, TMS

  12. #12
    Forum Contributor
    Join Date
    08-14-2014
    Location
    US
    MS-Off Ver
    2013
    Posts
    135

    Re: How to fix a vba code that returns a cell reference

    Okay have goodnight thank you for all your help with this. Look forward to you hopefully finding a way to modify this to work with other column groups.

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,771

    Re: How to fix a vba code that returns a cell reference

    OK, I think this works:

    Please Login or Register  to view this content.

    See the updated sample workbook.

    Regards, TMS

  14. #14
    Forum Contributor
    Join Date
    08-14-2014
    Location
    US
    MS-Off Ver
    2013
    Posts
    135

    Re: How to fix a vba code that returns a cell reference

    TMS that one confused me I tried to use it but all the data goes into A:E. However I think I have officially lost my mind either or one of two things happened the code fairy came and visited me last night or Excel is messing with me. I am leaning towards the latter. After tinkering with your code
    Please Login or Register  to view this content.
    And trying to adapt it to work for my need doing things like
    Please Login or Register  to view this content.
    so it would only work with columns G:K adding getting undefined expecting array errors and many others which is normal when I edit anything.
    For some reason I opened one of the other forms that had the previous code you wrote in it and it was working. So after almost an hour of testing, closing excel opening it back up and rebooting to see if it would act up again it hasnt.

    So can you test this on your system to see if it works for you please.
    The paper and pencil image opens the corresponding form for that group of columns. It should input all the data within those columns and insert the points received in the key after row 200. There are 3 images are for 3 different forms each controls one of the column groups. Each column group is a course so there are 3 courses.

    My excel did update lastnight so that might be why it is working now and I have 2013 if it makes a difference.

    The workbook

    Thank you for looking
    Last edited by Mr_Bill; 09-22-2014 at 11:30 AM.

  15. #15
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,771

    Re: How to fix a vba code that returns a cell reference

    Please Login or Register  to view this content.


    This line of code is used to determine the column letter to be used for the formula relative to a particular column. So, for columns 1 to 5, it will use "D", 7 to 11 will use "J", and 13 to 17 will use "P".

    So, you start with determining the numeric column number, lCol using a Match against the headings in Row 200. This (lCol) is used to select the text column letter, sCol from the array of possible column letters. It should be generic. That is, provided your drop down box has the appropriate headings, it should find the correct column and column letter fro the formula.

    I'll have a look but it seems, at first glance, as though you have reverted to the original "static" code.

    Thanks again fro the rep.

    Regards, TMS

  16. #16
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,771

    Re: How to fix a vba code that returns a cell reference

    Ok, generic code applied to all three forms.

    Please see the attached updated workbook.

    Regards, TMS
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    08-14-2014
    Location
    US
    MS-Off Ver
    2013
    Posts
    135

    Re: How to fix a vba code that returns a cell reference

    Was I going crazy or did the other one work correctly? For me it was installing all the right info and formulas. Thank you for the explanation on the vCol = Array part and a big thank you for your time on this. It seems to be working perfectly. Well as perfect as I envisioned it, I know there is a much better approach to doing this but I cant figure one out. In due time maybe, good work and thanks again

  18. #18
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,771

    Re: How to fix a vba code that returns a cell reference

    You're welcome.

    I didn't change any of the logic, just dropped it back in ... same place each module. And you'd defined some of the variables in some of the modules, but not all of the variables in all of the modules do I got some compilation errors. But, I've set them all up in the same way so you shouldn't have any issues.

    A lot of the code is simply repeated under each form so, in theory, you could/should make generic modules and call from the form code. But I'm afraid that's just a step too far

    Cheers, TMS

  19. #19
    Forum Contributor
    Join Date
    08-14-2014
    Location
    US
    MS-Off Ver
    2013
    Posts
    135

    Re: How to fix a vba code that returns a cell reference

    Found a problem with this one now TDS I am using the

    Please Login or Register  to view this content.
    And found one issue this is for the middle form which should only be reading the headers in G200:K200 but it is reading all the headers in Row 200. So if A200 has Homework and G200 has Homework as headers and I want the input to go in Homework it will stick it in A200 when it is suppose to go in G200 since this is the middle form. I tried to edit Rows(200), 0) to a 7 that didnt work.

  20. #20
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,771

    Re: How to fix a vba code that returns a cell reference

    That's your code, not mine. Not sure what you want me to say.

    My code does not specifically refer to a column letter. It picks it up from the array. It is generic code that should be applied, and work, for each form.

    Regards, TMS

  21. #21
    Forum Contributor
    Join Date
    08-14-2014
    Location
    US
    MS-Off Ver
    2013
    Posts
    135

    Re: How to fix a vba code that returns a cell reference

    okay no worries, I will make another post to see if this can be modified to work with the columns as a couple of the courses may have the same assignment names making this not work correctly. This is the code posted on http://www.excelforum.com/excel-prog...ml#post3841721 just doesnt have the part for assigning the column letter to the formula.

  22. #22
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,771

    Re: How to fix a vba code that returns a cell reference

    Sorry, just realised it WAS my code from an earlier version. But as you note, if you use the same column headings, it will always return the first match.

    You could, perhaps, get around this by doing the match in a subset of row 200 but have an increment for each form. You'd add the increment to the value to get the true column.

    Regards, TMS

  23. #23
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,771

    Re: How to fix a vba code that returns a cell reference

    See the re-worked example. This works even if the Column Headers are the same in each Form.


    Regards, TMS
    Attached Files Attached Files

  24. #24
    Forum Contributor
    Join Date
    08-14-2014
    Location
    US
    MS-Off Ver
    2013
    Posts
    135

    Re: How to fix a vba code that returns a cell reference

    Thank you so much TMS for taking the time to do this I appreciate it. I am very glad there are people like you who volunteer their time to help others out. Met a few on here who always go that extra mile to help out. Thank you again this works great .

  25. #25
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,771

    Re: How to fix a vba code that returns a cell reference

    You're welcome. Thanks for the rep.

+ 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. Is there function that returns MIN (or MAX) cell reference
    By riwiseuse in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-20-2014, 10:12 AM
  2. [SOLVED] formula returns a #VALUE! error if no value in reference cell???
    By ciapul12 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-12-2014, 05:39 PM
  3. [SOLVED] Cell Reference Returns #REF when data change
    By Hawk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-01-2013, 07:57 PM
  4. concatenated cell reference returns wrong value (by a quadrillionth)
    By kalmiopsis in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-02-2011, 03:10 PM
  5. Excel 2007 : Reference returns cell address - not data
    By Martin Snerl in forum Excel General
    Replies: 2
    Last Post: 01-15-2010, 05:23 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