I've been doing crazy searchs to try and find something like this......This is so close to what I'm looking for. How would I modify this script to make it a little more strict. I need to put this in about 100 cells I need it to see if the result is = to 5 if so extract text from Column A (Always) and make a list like this script is doing. I hope that makes sense.
Refers to this post:
http://www.excelforum.com/excel-gene...in-a-cell.html
Last edited by Ron Coderre; 11-06-2012 at 09:42 AM.
Infact I've changed just one thing and it's getting even closer. So if I change it to the script below I get 5,5. This is close. But instead of it grabing the text in that box. I need to get the in text from that same row from Column A. After seeing this I know it can work..... I'm actually getting really excited.
Please Login or Register to view this content.
Last edited by tray262; 11-08-2012 at 07:55 AM.
I think this code does what you want:
Does that help?Please Login or Register to view this content.
It work flawlessly!
I'm conparing it to the other script that you wrote. I'm trying to figure out how it knows to grab the info from column A.
So I'm thinking that it is in this line (RetVal = RetVal & cCell.Offset(ColumnOffset:=-ColRef + 1).Text & ", ")
I'm assuming that this line takes the current value of RetVal and adds sring of text from an offest position from the existing column. I'm a little stuck understanding this part of the script "Offset(ColumnOffset:=-ColRef + 1)" To me it seems that it would add +1 to the current column offeset?
Thanks so much, I hope all of that made sense, just trying to learn.
Also, I'm going to need six differnt verisions of this
one that looks for 5's, 4's, 3's, 2's, 1's, and 0's
So
And make 6 different ones. Would that be an acceptable way? Thanks!Please Login or Register to view this content.
Last edited by tray262; 11-08-2012 at 07:55 AM.
or could I add something like is?
Please Login or Register to view this content.
Last edited by tray262; 11-08-2012 at 07:56 AM.
You're getting close. Try this
Change
You can enter 5 into your function directly or use a cell referencePlease Login or Register to view this content.
Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
This updated code accepts a second parameter: MatchVal
which can be alphanumeric. The match is NOT case-sensitive...so "Dave" equals "dAvE"
Does that work for you?Please Login or Register to view this content.
WOW WOW WOW!
Works great.
So Function ConcatText(rng As Range, numval as String) As String
Collects my input say B1:B8 and combines it with my "real" value of 5 and makes into one string? Like B1:B85 or it keeps it seperate in the programing? or is this more of setting the var sow rng=a Range and numval=a string of letters, the thing that confusses is the As String at the of that statement.
I used to do Lisp programming years ago..so I'm really interested.
Thanks for all of you help!
The second parameter in the code posted by ChemistB and by me represents the value you want to find in the referenced range.Originally Posted by tray262
Using the code I posted....
This: =concattext(B1:B8,5)
means search the B1:B8 cells for the value 5 and process matching cells.
Does that help?
Yes sir, Thank you so much!
I don't want to beat a dead horse but after trying to use is this I'm running into one small problem. But just a question. I'm trying to add one more thing...... I the student name is equal to "0" don't add the name to the list. So I tried adding something and it didn't do anything. I think maybe it's a "but" function or something.
Thanks again, if there is no way to get it, it's not that big of a deal, just trying to use the same script to do two jobs. I'm determined to learn this.Please Login or Register to view this content.
Thanks!
End Function
Last edited by tray262; 11-08-2012 at 07:56 AM.
Can you post some sample data that exhibits the problem you want to solve?
To post a spreadsheet, Go Advanced > Manage Attachments
Also, for ease of reading, your code should be in code tags. See #3 of Forum rules for instructions (http://www.excelforum.com/forum-rule...rum-rules.html)
This_is what_I_have.jpg So this is what I have. I'm using it the same way as before. The only thing I need to add is if Let's say student 1, has 2 zeros that actually have assignmets in column A. So I want this function to grab the assessment name from column A unless the assessment name is Zero. Hope that make sense.
Thanks again.
Actually I think I got it after a little playing around. I think I'm starting to "get it" Please let me know if you see anything wrong with this. I added the sections in red. I think what I did was I added a 2nd check. So if it checks two things now. Please let me know what ya think. Thanks so much!
Please Login or Register to view this content.
Last edited by tray262; 11-08-2012 at 07:54 AM.
@ tray262...
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Sorry about that. I went through and fixed all of my posts with code. Thanks! I'm learning.
Last edited by tray262; 11-08-2012 at 07:57 AM.
1) You made a correction that does what you want.
2) I'd tweak it a bit to make it a little more readable and efficient:
Please Login or Register to view this content.
Thanks once again! I'm really trying to get the concept. Can you recommend any good book for this type of programming inside of excel. I'm going to try to and attempt to re-code what you've come up with and add a couple things to the function. I'm really having a lot of fun with this. This is what I'm going to try and do.
Basically the "UpOrOver As String" I'm thinking of making the program either be able to grab the info from the same column or if the data is going the other direction it will grab it from the row. So this is basically going to change if it's going to be a Row offset or a Column offset.Please Login or Register to view this content.
"WhichPositionToGet" I'm going to make a variable, instead of hardcoding that it is column 1 or row 1, I want to be able to set that in the function. I"m thinking that this will make it a lot more fuctionable for me.
I'll post some results. I think I've got the bug. Gotta go do some reading.
Thanks!
Tray262
Maybe not. lol.
Just getting #/Value. Any suggestions.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks