Posts by turtle44

Re: Color Coding Cells With Vba

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.

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:

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: Macro To Delete Vba Code - Macro Security Warning

Without seeing your code, it's difficult to say, but if your only concern is that you don't want the macro security prompt, why not just digitally sign your VB project?

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

Quote from microkid

What I want is, find the average of only things which are not zero in the column.

Then that should have been your question, and the title of your thread. If you had searched the forum, you would have found this:
http://www.ozgrid.com/Excel/average-without-zero.htm

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 Row When All Cells Are Empty

Why do you need more suggestions?

Re: Auto Replace A Text String With A "newline" (like an Alt-Enter within a cell)

This should get you started. You may need to add some more code to handle extra spaces.

Sub Test()
   Cells.Replace "%newline%", vbLf
End Sub

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.

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

Re: Limit Number Of Character Entered Into Cell

What doesn't it do?