Posts by luceze

Re: Convert Number To Year (literal)

You can use the DateAdd function in code.

DateAdd("yyyy",currentyrnum-1,(CurrentDt-IssueDt))

For Fourmulas, see Excel Date & Times

HTH,

Re: Saving To Server Location Rather Than Drive Letter

Use the UNC path.

\\YourServerName\PathToFile\YourFile.xls

HTH

Re: take slashes out of dates

If your value is truly a date value (date serial) the slashes are actually just formatting. Try formatting your cell as such

mmddyyyy

HTH,
Eric

Re: Using defined names in same workbook

Would the INDIRECT worksheet function not work for you?

=INDIRECT("TigerWoods")

Re: Highlight a row when "s" is entered

Norie's absolutely right.

Select column A, go to format\conditional formatting

Change "Cell Value Is" to "Formula Is".

Enter this as your formula:
=FIND(A1,"s")>0

Set the pattern to yellow.

HTH,

Re: Filter code

Can you post the larger file. You should be able to just change cell references.

Re: Macro Assistance: When Value of K1393=0, Prompt for Macro

This code will work. You might need to change some cell references. I wasn't sure which range you wanted to copy over.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("K2:K1392,K1394:K1395")) Is Nothing Then Exit Sub
    Dim myArr
    Dim cl As Range
    If [k1393] = 0 Then
        myArr = [h3:h22] 'populates array
        [a3:a22] = myArr 'places array in A3:A22
        For Each cl In [h3:h22] 'loops through values in Column H and adds 90
            cl.Value = cl.Value + 90
        Next
    End If
End Sub

Just right click on your sheet tab and select view code. Paste the code above in the code window.

Re: Highlight a row when "s" is entered

Hello,

See if this is what you want:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column <> 1 Then Exit Sub
    If InStr(1, Target.Value, "s") Then
        Target.EntireRow.Interior.ColorIndex = 27
            Else
        Target.EntireRow.Interior.ColorIndex = xlNone
    End If
End Sub

HTH,

Re: Filter code

Here you go!

I just now changed the filter range to include your column headings.

Re: Macro Assistance: When Value of K1393=0, Prompt for Macro

Is the value of K1393 a calculated field or will a user enter a value there?

Re: Filter code

Where are you getting the error?

Re: Workbook Name - Not Indirect!

Hey JD,

Sub MySubNeedsAName () 
    Dim BookName As String
     
    BookName = ActiveWorkbook.Name
     
     'the rest of my sub here
     
End Sub

HTH,

Re: Filter code

Hey Kim.

Change this line:
ActiveSheet.ShowAllData

to

If Sheets("Portfolio_Filter").FilterMode = True Then
Sheets("Portfolio_Filter").ShowAllData
End If

HTH,

Re: Highlighting Rows based on Value

Hey Taylor,

Conditional Formatting is the way to go here.

Select all of the cells in your worksheet.

Go to Format/Conditional Formatting.

Change "Cell Value Is" to "Formula Is"

Paste this in the box to the right.

=IF($A1<>"",IF($B1=4,TRUE,IF($B1=6,TRUE,FALSE)),FALSE)
You will need to change the reference to column B to the column that contains the data you want to test.

Click the format button and on the patterns tab select the color yellow.

Click OK.

HTH,

Re: Copy rows based on conditional statement

You can use the autofilter function to do this.

See attached for example. (The macro is called FilterCopy)

HTH,
Eric

The countif function has the following arguments:
=countif(Range,Criteria)

Range is the list of values that contains the value that you are trying to count. Criteria is the value that you are trying to count.

Second question:

When you drag a formula down the cell references are automatically adjusted to the current row and/or column.

If you state the references as absolute the references will not adjust.
Example:
=countif(I1:I20,I20)
When you drag this formula down any reference not enclosed in a "$" will change. If you drag it down one row it will change to
=countif(I2:I21,I21)

To properly use the function that Mhabib suggested then create absolute references in the formula.

=countif($I$1:$I$20,I1)

When you drag this formula down the range argument reference will stay static but the criteria range will adjust as you drag it down.

HTH,
Eric

Here's a neat little snippet of code that I picked up some time ago that will do what you want. Not sure who to give the credit to.

Private Sub Test()
    CreateDirectory ("\\Server\Partition\Directory\Sub1\Sub2")
End Sub
    
Public Sub CreateDirectory(sPath As String)
On Error GoTo DirectoryError
    
    Dim sPathPart As String
    Dim i As Integer
    
    If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
    
    For i = 1 To Len(sPath)
        If Mid(sPath, i, 1) = "\" Then
            sPathPart = Left(sPath, i - 1)
            MkDir sPathPart
        End If
    Next i
    
DirectoryError:
    Resume Next
    
End Sub

P.S. The strikethrough W's are supposed to be backslashes. Anyone know how to keep the compiler from converting them?
HTH,
Eric

Try this:
=B2&"-"&B3

Where B3 is the rest of your code.

HTH,
Eric

Something like this...

Set cboTest= CommandBars("YourCommandBarName").Controls("objCBC")
With cboTest
   .Text = strText
End With

HTH,
Eric

Select your range. Go to Data\Text to Columns. Choose whatever delimiter that you want to use, click next, select your data types for the destination fields, click finish.

HTH,
Eric