VBA CODE for Excel 2007 & 2010

    I need help in writing a code for searching a column (Say: AS) for text "TRUE". When the code finds a "TRUE" cell I need it to copy that entire row and paste into sheet 2, row 2 for the first entry and continuing downwards for any next matches. I need it to begin searching at Sheet One, column AS, row 2 and looking down the column. I have found some examples and used them. The code makes it to end sub but it does not have any results on sheet 2 when I know there should be a handful. The document I am trying to complete it in is attached. I was afraid it may not read cells that have formulas. For AS column, I used "AND" function to generate true or false. If I can be picky, I would also like for the code to maintain formulas when copying to sheet 2. I greatly appreciate any help that I can receive. Thanks guys! Macro is in workbook.

    Re: VBA CODE for Excel 2007 & 2010


    Thanks a lot... it worked beautifully. Is there a way to Create a button (Say: Add Property) and have a custom box that wil ask for user input that can add my entries to a new line? If so I can type up the idea and see what can be done. I highly appreciate your help... I was stumped on it all day! I think you just motivated me to take some classes on VBA.

    Re: VBA CODE for Excel 2007 & 2010


    Do you want this input box to be a seperate action from the copying you're doing now?


    As in, have to buttons...one to copy as before and one to get a user input?


    Sounds like, so I'll work on that. If not, please advise.

    Re: VBA CODE for Excel 2007 & 2010


    On the same excel sheet that I attached.... forum.ozgrid.com/index.php?attachment/44882/ .... I would like a button in the upper right hand corner when the sheet loads that has a button that says "Add a property". When clicked I would like a box to pop up with a header and a user input box to the right of each header that allow me to input certain amounts. The text headers would idealy be:


    Property Information (Title of Box when opened)


    Address [ ] User input would need to accept fairly lengthy addresses so anything you can do to maximize would be great
    City [ ] 15 letters?
    State [ ] 2 letter combos
    Zip Code [ ] 5 digit
    List Price $[ ] 9 digits whole number
    Market Value $[ ] 9 digit whole number
    Asking Price $[ ] 9 digit whole number
    Taxes $[ ] 9 digit whole number
    Days Listed [ ] (would also be nice if it would add one per day that passes in real time. For example I input 23 when adding but tomorrow the excel sheet says 24)
    # Bedrooms [ ] Only needs room for 3 digits
    # Bathrooms [ ] Only need room for 3 digits


    Button: "Add Property" (will automatically send the above info to corresponding cells on worksheet at first available empty line)
    Everything to right of [] above is notes and not needed in pop up box.


    All the other cells in a row have formulas that will calculate. Is it possible to write into macro that when button: "Add Property" is clicked and info adds to bottom of list that the formulas drag down to accept conditional formatiing and auto calculate versus me having to drag it down with each new property? I usually drag the corner down for each column header not mentioned in above box layout.


    Is this possible... Thanks for your expedient replies and excellent help. Sorry if it is a lot. I have attached my spreadsheet with your macro in it. I would like it saved on this workbook if possible.


    Thanks for all the help... You have been great!

    Re: VBA CODE for Excel 2007 & 2010


    Oh yea I like the two button look except the sheet it is on looks like an older version of the sheet. That in the edition attached to my previous post would be great.
    Say on sheet one in upper right hand corner if possible but if not anywhere on sheet 1 is fine. The copy Trues button will run the macro? Sweet! Let me know if you have any more questions for clarification and I cannot tell you how glad I am that you are helping with this!

    Re: VBA CODE for Excel 2007 & 2010


    The box I describe earlier is what I would like for the user input box macro.... hope I am not confusing you.... doubt it since you understand code lol

    Re: VBA CODE for Excel 2007 & 2010


    LOL...I think I understand, but it sounds like a userform would be better. I'll see what I can come up with.


    I do have another question though.


    RE: The user input code:
    Is this to copy something from one sheet to another, or is this to enter a totally new property?


    Since the coding is already done to copy from one sheet to another, I'll work on the latter for now.

    Re: VBA CODE for Excel 2007 & 2010


    I actually like both... On sheet 2 you can put the two buttons you had. That is cool. For sheet one I would like an user input box (maybe wrong term used) to enter an entirely new property to the bottom of the list in sheet 1. I want it to auto populate the feids when I click "Add Property" and also to drag down the formulas towards far right of sheet one so that the new entry can have the conditional formatting and formulas wil calculate. I do like the two buttons on sheet two though.


    I hope I dont become a pain in the arse, but I want to delete the column labeled Zestimate. I am afraid to mess up the previous code by doing so as it would change the "TRUE" column to column "AR" instead of "AS"

    Re: VBA CODE for Excel 2007 & 2010


    Ok, this is a start.


    Few notes:
    -I deleted the old column G "Zestimate" and adjusted the Search code for that.
    -Created a userform to add new properties to sheet 1. There is a button for that on sheets 1 AND 2, but it will only add new ones to sheet 1.
    -As far as the formulas, you could drag those down as far as you want...the add property code doesn't affect those columns.
    ------If you don't want to see the "0"s, for instance in column H, you could use =IF(F4="","",F4*.85). I didn't do that yet, but it's a simple matter.
    -The user input to copy will copy whatever address they type in FROM sheet1 TO sheet2.
    - Haven't messed with getting the "days listed" to update daily yet, but I will when I get home tonight, if you haven't by then.
    - On the userform, if you start in the address box, as you push enter it will go across then down. I.E.-to city, then state, zip, etc, etc. Bad tab order is very irritating, IMO, so I try to set it to work correctly, lol.
    -I took a few liberties. For instance, I inserted a new row above the headers for buttons and such...and froze the header row on each sheet for easier readability.


    Hope this is getting closer to what you want. We can tweak it some more to suit your needs as time goes on. :)


    forum.ozgrid.com/index.php?attachment/44883/

    Re: VBA CODE for Excel 2007 & 2010


    Yet another update:


    Some more thoughts:
    -One way you could make the days listed update is to (in a remote column) add a date that is (days listed) days before NOW(), then put a formula in the Days Listed column subtracting that date from NOW(). If that interests you, you'll have to create the column and post the date posted for all the properties you have listed.
    -All the tools are now in one userform. Try it out and see what you think.


    Any other ideas, I'll give it a shot.


    forum.ozgrid.com/index.php?attachment/44886/

    Re: VBA CODE for Excel 2007 & 2010


    I LOVE it! I love these buttons, you did a GREAT job!!!!


    I like the idea about not seeing 0's where there is no data, but some columns have formulas that look for the info in the userform for adding new properties and will autocalculate based on the other entries. If I add the "if" function won't it erase the formulas that exist?


    It would be better if there be an addition to the macro that when "Add Property" is clicked that after the existing macro is done inserting it, it grabs the columns with no data on new entry row and drags down the formulas from the cell above? This way there is no need to see values when they don't exist, but when a property is added it will automatically drop the formulas from columns H, M, N, O, P, Q, S, U, X, Y, Z, AB, AD, and AF-AR.


    Can we also add "Advertising" to the new entry user form to drop the user input data into column W?


    If it is possible to accomplish this... that would pretty much be PERFECT!


    Thanks!

    Re: VBA CODE for Excel 2007 & 2010


    forum.ozgrid.com/index.php?attachment/44893/


    Ok, this one should do all that. Still haven't added the automatic "days listed", because I don't have the original added dates, but that's a fairly simple affair.


    If you want to add that, the code will have to be changed a bit though so if you have trouble let me know.


    Don't be afraid to experiment either :) ....I keep everything I make in a file, so I'll have a backup (as you should as well, if your going to change things that are untested, not just on this workbook, but any workbook).


    Anyhow, this one:
    -On a new property add, will copy down the last entire row before posting the new property, then overwrite the appropriate columns with the new data.
    -Has the requested box for advertising, and adds to column W.


    If you have any other problems/changes needed let me know. Believe it or not, I enjoy trying to do new things in excel....guess I'm a nerd lol..............................but I can live with that. :)

    Re: VBA CODE for Excel 2007 & 2010


    No shame in being a nerd... Look what you can do with it... Something I have been trying to figure out for the last six months and you did it in a few hours. It is absolutely incredible. I wish I had the knowledge you have with VBA code. I am definitely going to take classes now because I was proud of getting your work and can only imagine how proud I would be if I wrote the thing. Thanks for all your help. If you want my email is "[email protected]"[/email]... If you are okay with personal correspondence feel free to shoot me an email. I won't bother you much more lol... Thanks for the help you have been great!

    Re: VBA CODE for Excel 2007 & 2010


    Sorry our internet went down here in Afghanistan last night. For some reason it doesn't copy the data over for columns AB and AD. Any ideas?

    Re: VBA CODE for Excel 2007 & 2010


    Which sheet are we talking about? And which operation? Adding a new property or copying a user choice over or copying all "good investments"?


    The filling of the formulas happens for the entire row before the new property is posted. I'll look into the other 2 operations in the mean time and see what happens.

    Re: VBA CODE for Excel 2007 & 2010


    Ok, first guess.......


    On sheet 2, columns AB and AD do copy over and the formulas are there, but the results don't show up because the column is too narrow.


    I didn't really pay much attention to that before...I figured the narrow columns were just blank columns for spacing.


    The values are there...widen the columns if you want to see them :)

    Re: VBA CODE for Excel 2007 & 2010


    oh okay... You are correct... AB AD both have data in them and the others are purposely left blank for formatting.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login