Posts by malathion

Re: Selecting and Deleting every 24th row?

I haven't tested this but it should work. BACK YOUR DATA UP BEFORE YOU RUN ANYTHING LIKE THIS.

Sub Macro1()
Dim i As Integer
Dim RowsCount as Long


RowsCount = ActiveSheet.UsedRange.Rows.Count


For i = 1 To RowsCount
    If i Mod 24 = 0 Then
        Rows(i).Delete
     End If
Next i


End Sub

Also, beware of the fact that after you delete a row, the index of all the rows beneath it will change. You may need to increment your index after deleting the row. The code I wrote will not do that since I didn't know which behavior you wanted.

Re: Selecting and Deleting every 24th row?

Edit: derp, one minute

Re: when a number reaches "X" the cell defaults to zero and adds one to another cell

In the code you pasted you were modifying H15 not F15. I changed it below.

Sub Add1()


Dim Value1 as Integer
Dim Value2 as Integer


Value1 = Range("G15").Value
Value2 = Range("F15").Value


If Value1 = 15 Then
     Range("G15").Value = 0
Else
     Range("G15").Value = Value1 + 1
End If


Range("F15").Value = Value2 + 1
Beep
End Sub

Re: when a number reaches "X" the cell defaults to zero and adds one to another cell

Quote from craigswear;628621

I need a VBA code that will meet the following conditions: when cell g15 goes higher than 15 it should return to zero and start over again. At the same time it should add 1 to cell f15. Any ideas?

Sub Add1()


Dim Value1 as Integer
Dim Value2 as Integer


Value1 = Range("G15").Value
Value2 = Range("H15").Value


If Value1 = 15 Then
     Range("G15").Value = 0
Else
     Range("G15").Value = Value1 + 1
End If


Range("h15").Value = Value2 + 1
Beep
End Sub

Re: Need VBA method to "bake" conditional formatting, ie make it permanent

Speaking only for myself, it absolutely would be acceptable.

Re: Need VBA method to "bake" conditional formatting, ie make it permanent

No. The method did not work in Office 2010 and I shelved the project for the time being.

Re: Need VBA method to "bake" conditional formatting, ie make it permanent

It does need to be generic. Now that you've replied I've realized how I'm going to do this, and it will be easy enough that I'm amazed this hasn't been published already. I plan to post it back here once I get it working, but in case I forget, here's the outline I have in mind:

My VBA code will read the existing conditional formatting rule on the cell. It will test the cell to see if the condition was matched. If it was, it will apply the formatting.

Luckily with some googling I am halfway there, since someone has developed a method for reading cell color regardless of whether it was set by cell formatting or conditional formatting: http://www.vbaexpress.com/kb/getarticle.php?kb_id=190

I will report back.

Re: Need VBA method to "bake" conditional formatting, ie make it permanent

Bump. Anyone?

Re: Need VBA method to "bake" conditional formatting, ie make it permanent

Quote from AAE;614528

Yep. Start with the macro recording going through the steps of:

Selecting the cell range
Deleting all Conditional Formats
Applying formats equal to what was applied in the CF condition.

Afterwards, post your code and workbook (dummy data) for helping in refining the code.

I must have misunderstood you because this gave me code for deleting all the conditional formats and then setting arbitrary formatting. I need a way to copy the result of the conditional format to the cell format programattically, and cell by cell.

Quote from mikerickson;614529

What version of Excel are you using?

2010

I need a method for converting "conditional" formatting in a cell to normal cell formatting (ie, not conditional). Any advice?

Re: Creating New Dynamic Ranges After Copying a Worksheet

Please answer my question instead of posting non-working code. I can write working code for you if you can help me understand your issue.

Re: Creating New Dynamic Ranges After Copying a Worksheet

So the formulas are already defined on each worksheet, and you just need to name the ranges?

Re: Time formating and calculation

AAE, you misunderstood my post. He PMed me thanking me for the solution in the post above the one you quoted.

Re: VBA: Copy rows by criteria and insert in location by criteria

Your request is hard to follow. Can you post what you have? You can obfuscate confidential data if necessary. But it would help a lot to have a look at it so I can understand your question.

Re: Creating New Dynamic Ranges After Copying a Worksheet

Something like this, haven't tested though

Sub FillFormulas()
    Dim ws as Worksheet
     
    For Each ws in Worksheets
            Range("A1").Formula = "=OFFSET(" & ws.Name & "!$J$3," & ws.Name & "!$T$2-1,0," & ws.Name & "!$T$3,1)"
            Range("B1").Formula = "=OFFSET(NewCoding13.xls!XValues,0,1)"
            Range("C1").Formula = "=OFFSET(NewCoding13.xls!XValues,0,2)"
            Range("D1").Formula = "=OFFSET(NewCoding13.xls!XValues,0,3)"
            Range("E1").Formula = "=OFFSET(NewCoding13.xls!XValues,0,4)"
            Range("F1").Formula = "=OFFSET(NewCoding13.xls!XValues,0,5)"
            Range("G1").Formula = "=OFFSET(NewCoding13.xls!XValues,0,6)"
            Range("H1").Formula = "=OFFSET(NewCoding13.xls!XValues,0,7)"
    Next ws
End Sub

Re: Excel formula problem

That seems really complicated. Dunno why this wouldn't work:

=IF(Q2=U2,1,S2+1)

Re: Time formating and calculation

FYI, he sent me a PM saying this resolved his issue.

Re: Time formating and calculation

I wouldn't use the 00:00:00 column for this at all, honestly. Work with DurationS to do your math and then convert from seconds to minutes. See attachment, hope this helps

My company represents a major steel manufacturer. I have a workbook that draws data from an Access database into a table on Sheet 1. That table then feeds a pivot table on Sheet 2 with columns organized by ship date -> PO number -> $ value. The PT sums the values of our open orders by month and uses a calculated field to show the difference between the production budget for the month (set in dollars) minus the orders already booked for production.

The problem is that right now, I am using a constant as the budget for every month that approximates the actual available production capacity. But in reality this number is volatile. So what I want to do is figure out some method to display available production capacity where the budget is also variable and can be set by the user (me).

My first try involved adding a separate table and adding the budget for the month as its own column in the table on Sheet 1. I thought I could then use the calculated field. Of course this didn't work since the sum for the monthly budget gets multiplied by the number of open orders for that month, so the production capacity is grossly over-valued.

Any suggestions as to how to implement this?

Re: Dependent Data Validation Lists

A pivot table?