Posts by turtle44

    Re: Color Coding Cells With Vba


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case UCase(Left(Target, 3))
       Case "VOL"
          Target.Interior.ColorIndex = 5
       Case "MOM"
          Target.Interior.ColorIndex = 6
    End Select
    End Sub

    Re: Automatically Insert Row When Row Value In Column Changes


    Quote from M21121970

    I'm unable to find VBA code to insert a blank row when the value in Column L changes.


    I'm assuming you mean "when the value in L is not the same as the previoius value" and not "when someone changes a value in L". If that is the case, then this should work.

    Code
    Sub InsertRowAtChangeInValue()
       Dim lRow As Long
       For lRow = Cells(Cells.Rows.Count, "L").End(xlUp).Row To 2 Step -1
          If Cells(lRow, "L") <> Cells(lRow - 1, "L") Then Rows(lRow).EntireRow.Insert
       Next lRow
    End Sub

    Re: Macro To Delete Vba Code - Macro Security Warning


    I also agree, but if you are not comfortable with creating Add-Ins, couldn't your code simply be in another workbook. The workbook with the code could open the relevant workbook, run all of the macros, then save the workbook without there ever being any code in it.

    Re: Filter Data From Another Worksheet Based On Validation List


    Quote from enthrop

    In a way, I'm looking to do what's similar to a filter, only on a different sheet without having to copy all the data.


    That's exactly what an Advance Filter does. Since it sounds like you want to make it "automatic", you will probably need to write some code to rerun the filter whenever the value changes.

    Re: Copying Different Values A Number Of Times


    Quote from gvelix

    I need to copy 12 times...I got like 200 records that will be converted in 15000 aprox.


    I'm not sure how 200 * 12 = 15000, but maybe that's just me.
    Try this:

    Code
    Sub Copy12()
       Dim lRow As Long
       For lRow = 1 To Cells(Cells.Rows.Count, "A").End(xlUp).Row
          Range(Cells((lRow - 1) * 12 + 1, "C"), Cells(lRow * 12, "C")) = Cells(lRow, "A")
       Next lRow
    End Sub

    Re: Macro To Delete Vba Code - Macro Security Warning


    I agree with dcraker, but if you are still unable to find your mystery macros, you could just copy all sheets into a new workbook. The new workbook will have the same data, but no macros. You may even be able to eliminate the step of deleting your macros if you take this approach.

    Re: Macro To Delete Vba Code - Macro Security Warning


    Quote from richadj4

    Turtle: That would work except that my IM department are being unco-operative. They are of the opinion that any file with a macro should at least advise users that it is there.


    Everyone would still be advised of the macro, unless they explicitly changed they're settings to automatically trust all macros with your digital signature. This isn't what I was suggesting, I was simply suggesting that you could sign your macros so that YOU would not be prompted when opening.

    Re: Select Range Based On The Column The Function Is Executed In


    I'm not sure why you need a UDF. Doesn't this simple formula do what you need?
    [bfn]=IF(SUM(A$2:A$2477)=0,0,AVERAGE(A$2:A$2477))[/bfn]

    Re: Vba Programming


    Quote from user1100

    As I said I am a noob and don't know the proper terms to put in the title.


    It has nothing to do with knowing terms, it has to do with using a title that describes your question. Every thread could be titled either "VBA Question" or "Formula Question", but that's not very desciptive.

    Quote from user1100

    I guess I could of used "selective copy".


    That would have been better. I would have used "Copy Based on Criteria", since that is what your question asked. If you had used a more descriptive title, the "Possible Answers" section of this page could have given you an answer immediately.
    As to your question, I stick with my original answer; use Advanced Filter.

    Re: Vba Programming


    Welcome to the forum. However, you may want to re-read the rules because "VBA Programming" is not a descriptive title. It sounds like you just need to use an advanced filter to copy your data, but your question rather vague so it's difficult to say for certain.

    Re: Create An If Event Based On Day Of The Week


    Welcome to the forum. However, I'm pretty sure that one of the rules you just agreed to when joining is "one question per thread". You seem to have 3. If you pick one, I'll be happy to assist you.

    Re: Explanation Of Syntax


    Based on your thread title, "Explanation of Syntax", it sounds like you want the definition of "syntax", so here you are:
    "SYNTAX: The rules governing the formation of statements in a programming language."
    If in fact this is not your question, perhaps you could explain which part of the code you don't understand (and title your thread accordingly), and then I'm sure someone will be happy to explain it to you.

    Re: Delete Row When All Cells Are Empty


    Quote from Designer_6

    to your statement. I don't get what you try to say ".... the Possible Answers above your question seem to ask the exact same question."


    If you look above your question, there is a section on the page labeled "Possible Answers". The links in this section take you to other threads which have asked the same question. When you submitted your post, you should have been prompted to review the "possible answers", because then you could have found your answer without the need to start a new thread.

    Re: Delete Rows When All Cells Is Empty


    Personally, I would use advanced filter to extract the records that aren't blank, but if you want to delete the rows, the Possible Answers above your question seem to ask the exact same question.

    Re: Delete Rows On Dynamically Changing Sheet Names


    Quote from pukks

    I now however want to delete all of the rows with information in that exist in all of the other sheets other than the 'data' sheet.


    Are you deleting all rows, or just certain rows? Either way, this should get you started.

    Code
    Sub DeleteRows()
       Dim WS As Worksheet
       For Each WS In ThisWorkbook.Worksheets
          If UCase(WS.Name) <> UCase("Data") Then
             'your code to delete rows here
          End If
       Next WS
    End Sub