Posts by Rowddawg

Re: dependent dropdown size depends on selection

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

Thanks, Cytop. See above.

Only one set goes through 4 levels, the others would not have information at that point. Maybe I leave it as is, and create a cell that returns which section is used last, and pulls the information from there... Not sure.

Thanks all.

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?

TIA.

*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...

Re: fix calculation error in worksheet

Thanks again holycow. I'll give it a shot in the morning when I'm back and see.

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

Edit your post, select your code, then click the # key above this area... that will show your code properly.
Thanks.

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

Wow. Very nice. Started going through that code looking at what each bit does, and began truly realizing the scope of what I don't know.
Very nice indeed Jindon.

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.
Delimited
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: spliting single cell data w/ spaces btwn the data into new cells / pdf to Excel c

I'm about to leave work to head home. It will be later tonight before I can revisit this, but I will work more on it this evening.

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

Will the Pdf format change, or is it ok to plan on this format being constant? (even with multiple pairs of pdf sheets)

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

Check your pm. Working on helping you, but you need to look at one thing first.

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

Thanks Cytop. I'll adjust that now and keep messing with it.

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.

Thanks.

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.

Sub ResetOctober()
'
' Reset October
'
'
    Dim actual As Range
    Dim projection As Range
        
    Set actual = Range("f6:f26")
    Set projection = Range("d6:d26")
    
    
    On Error Resume Next
    actual.Select
    Selection.Copy
    Selection.Offset(0, -4).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    projection.Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
    actual.Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
        
    actual.Select
    Selection.Offset(0, 7).Select
    Selection.Copy
    Selection.Offset(0, -4).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    projection.Select
    Selection.Offset(0, 7).Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
    actual.Select
    Selection.Offset(0, 7).Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
        
    actual.Select
    Selection.Offset(0, 14).Select
    Selection.Copy
    Selection.Offset(0, -4).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    projection.Select
    Selection.Offset(0, 14).Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
    actual.Select
    Selection.Offset(0, 14).Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
        
    actual.Select
    Selection.Offset(0, 21).Select
    Selection.Copy
    Selection.Offset(0, -4).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        projection.Select
    Selection.Offset(0, 21).Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
    actual.Select
    Selection.Offset(0, 21).Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
    
    Range("a1").Select
    
    
End Sub

Re: Hide or Lock column based on cell date

Anyone?

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:

aCell.Select
    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.

Sub find()
    Dim oSht As Worksheet
    Dim strSearch As String
    Dim aCell As Range
            
    On Error GoTo Err
    
    Set oSht = Sheets("Production")
    
    ActiveSheet.Unprotect ""
    ActiveSheet.EnableSelection = xlUnlockedCells
    strSearch = Date
    
    Cells.Select
    Selection.Locked = True
    Selection.FormulaHidden = False
    
    Set aCell = oSht.Range("F3:p3").find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    
    aCell.Select
    ActiveCell.Offset(1, 0).Select
    Range(ActiveCell, Cells(ActiveCell.Row + 6, ActiveCell.Column)).Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    ActiveSheet.Protect ""
    
    Exit Sub
Err:
    MsgBox Err.Description
End Sub

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.

Re: Count unique occurances based on multiple conditions in another cells

Thanks, sorry for misunderstanding. Just looking at it to see what's going on...