Posts by Rowddawg

    I've found multiple sources listing a myriad of ways to create a dependent dropdown list that either uses data validation and other options.

    Is it possible to vary the total number of dropdown lists based on what is needed?

    Say I have 3 items in a main list. The first and second have 3 levels of selections that could be selected from a dropdown list, but the third has a fourth.

    How can I tailor the sheet to show the correct number of lists? I.e. the first two would only show 3 dropdowns, the third would show 4?

    Or is it possible?


    *also, this is very new in the creating phase, although I can make a dummy sheet showing what I'm saying if it isn't clear...

    Someone at my accounting department had an issue, and asked me to help. If needed I can make a dummy worksheet, but I hope what I'm describing will make sense.

    I have sales for an entire component, that is split into 20 locations within that component.
    We show sales and taxes on those sales for the entire component, and then for each location.

    The disparity comes when the taxes for each location do not add up to the total taxes for the component. It is off by a few cents at this point, but they want to make sure they are accurate.

    Thanks in advance.

    Re: spliting single cell data w/ spaces btwn the data into new cells / pdf to Excel c

    Tried for a while to get around the importing from pdf, not going very well as of yet.

    While dealing with your example though, had a little progress.

    Ok... few steps here, but best I can do as of right now...

    Lets look at cell F3 in your example.
    Right now the soft line breaks are causing excel to not seperate the numbers.
    If you highlight that cell, and select replace, then under the replace what section hold alt down, then press 010 on the keypad.
    Under the replace with section, enter a single space.

    That will add a space between every item in that cell, by replacing every soft line break with one.

    Next, select the cell and go to the data tab.
    Select text to columns.
    Only select spaces when given the option,
    and leave general in the next section.

    Once that is done, you'll have all your information in one row.

    From there, select the entire row, and Copy it.
    Right click, and select paste special. Then select transpose.

    That will paste the information in a vertical format for you, which will allow you to select the columns you need and paste the information where you need it.

    It will work with any cell that has multiple information in it, like F3 does in your example.

    Still quite a few steps to get where you want, sorry for that. Will try to work on this as I can, but at least as it stands now you can start sorting the data. Also been working on basic macros to to text to columns and then transpose, but not sure if that would be a route you'd like to go.

    Re: write this code more efficiently

    Thanks again for all the help. Just got the code working for an entire year, so our controller can fix about 20 locations in roughly 10 minutes instead of having to copy/paste etc for the entire sheet for all those locations... Still a bit lengthy, but thanks to Cytop and Venkat's help it effectively cut the code by more than half from what it would've been...

    And it is giving me a better understanding of how to use vba, which is great.
    Thanks to you both, again.

    Re: write this code more efficiently

    When I use the revised code, it is currently pasting formulas and constants, not just pasting values. Any idea why?

    Original code had it pasting the value from the actual range into the offset area of 4 columns over.


    Re: write this code more efficiently

    Thank you very much. This is exactly what I was wanting to know. I thought it was redundant to select something, then take that selection again and copy, etc... but when I tried shortening it I ran into some errors.

    Again, thank you very much.

    Hello everyone.
    I'm fairly certain there's a faster way to do this. Much like previous posts very new to vba, just trying to learn how to do stuff in a more efficient manner. Created this to clear a financial sheet for one month, to get started on the next fiscal year. The three sets past the original set are offset to clear the remaining weeks in the month. (only 4 weeks in this month of the period.) Everything is 7 columns past the previous week, which is why the offset(0, 7). The offset(0, -4) is to move actual from this year to prior year in what will be next year's setup...

    Attached is a dummy month
    Thanks in advance for any help.

    Re: Hide or Lock column based on cell date

    So... realized in the original post that prior dates need to be accessible as well...

    Tried to make a loop that worked back to range (F4:F10), and upon hitting that range stopped, but am being unsuccessful so far.

    Can anyone give a hand? This is what I need to loop back to that range...

    Selection.Offset(0, -1).Select
        Selection.Locked = False
        Selection.FormulaHidden = False

    Which would go right after:

        ActiveCell.Offset(1, 0).Select
        Range(ActiveCell, Cells(ActiveCell.Row + 6, ActiveCell.Column)).Select
        Selection.Locked = False
        Selection.FormulaHidden = False

    Pretty sure I'm messing up the syntax etc with the loop I've tried, which was going to be a do:until loop.
    After that this should be a solution to the request.

    Re: Hide or Lock column based on cell date

    Sorry for taking longer than I said... Had a family issue yesterday that I had to take care of.

    Add the following to a form control to run. Works for me so far... trying to get it to run without the form control, but haven't been successful so far, so if anyone else much more knowledgeable can help, would be appreciated. Tried to remove the redundant pieces as I pushed together a few things to make it. Hope it works for you. It selects the cells directly in the column that today's date references and unlocks them. Added a blank password, change the "" to "whateverpasswordyouwant" to add an actual password to the sheet.

    Re: Hide or Lock column based on cell date

    I have been dancing all around this with recent posts both of my own and others... trying to create a solution specific to your need though.
    Unfortunately I've been up 19 hours after getting 3 hours sleep last night, so my mind is in an infinite loop of it's own.

    I will take another shot at this in the morning once I've had a little more sleep if no one else has helped by then.