Posts by jaslake

Re: Edit data in userform without creating a duplicate entry 2

Hi cyycolin

I agree with royUK. His Data Base Form is easily adaptable.

Your Workbook is attached, with Code, that does this

Quote

how can I display three records of "14" correctly?

Re: Access workbook from another workbook

Hi San

It simply means that I'm aware it can be done but I've not done it and would have to develop the skill to do it (put it in my toolbox).

Re: Edit data in userform without creating a duplicate entry 2

Hi cyycolin

Are you interested in the ComboBox INSTEAD of the ListBox...or do you wish BOTH?

Quote

also display the search result in Combo Box

Re: Access workbook from another workbook

Hi San

This can be done but it's not in my personal toolbox.

Quote

1. I do not want to open the file. In other words, I want to simply perform the "CountIf ..." operation on the other file without opening it.

To me, this sounds like a UDF and is beyond the scope of this Thread.

Quote

I can call this function from a cell and assign this value to it

I'd suggest you start a new Thread for these issues and make sure your Thread Title is descriptive of your requirements.

Re: Access workbook from another workbook

Hi San

Try this (tested)...modify the Sheet Name as required.

Option Explicit
Sub test()
   Dim wb1          As Workbook
   Dim WasOpen      As Boolean
   Dim NumOccurances As Long
   If wb1 Is Nothing Then
      Set wb1 = Workbooks.Open("C:\Temp\PROJECTS\Book1.xlsm")
   Else
      WasOpen = True
   End If
   wb1.Activate
   With Sheets("Sheet1")   '<---Modify Sheet Name
      NumOccurances = Application.WorksheetFunction.CountIf(.Range("G:G"), "f")
      MsgBox NumOccurances
   End With
End Sub

Re: Macro has trouble running similar combined code

Good work...glad you got it working.

Re: Macro has trouble running similar combined code

Hi jace

Don't know. If you can upload a de-sensitized version of your File (with existing Code) I'll be glad to look at it with you.

Re: Access workbook from another workbook

Hi San

Don't know...untested...you can try this

Dim wb1          As Workbook
   Dim NumOccurances As Long
   wb1("C:\Temp\PROJECTS\Book1.xlsm").Activate
   With Sheets("Your Sheet Name Here")   '<---Modify Sheet Name
      NumOccurances = Application.WorksheetFunction.CountIf(.Range("G:G"), "f")
      MsgBox NumOccurances
   End With

Re: Macro has trouble running similar combined code

Hi jace62

Try this. Set ws to Nothing between iterations

Set ws = Nothing

How many Worksheets are you trying to copy?

Re: Call macro on worksheet from BeforeClose event

Hi laguna

Perhaps this

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Call Sheets("Sheet1").ExportOrders '<---Change Sheet Name as appropriate
End Sub

Re: With worksheet.range(cells)

This works for me

With wsWorksheetName 
    .Range(.Cells(1,1),.Cells(1,4)).Select 
End With

Re: Filtering Data with between dates using a userform

Hi nobby

Using your UserForm try this

Private Sub Search_Click()
  Dim fromDate As Date
  Dim Todate As Date
  Dim rng As Range
  Set rng = ActiveSheet.Range("A1").CurrentRegion
  fromDate = TBFrom.Value
  Todate = TBTo.Value
  With rng
    .AutoFilter Field:=2, Criteria1:= _
                ">=" & fromDate, Operator:=xlAnd, Criteria2:="<=" & Todate
  End With
End Sub

Re: Automaticly hide rows based on cell value

Hi Martin

Ditto

Quote

Board policy is that solution are not given for School/College/Other Training courses, but suggestions can be...

I'd consider using a Select Case Statement in your ComboBox Code. Depending on the Amortization Period Selected you would Hide (or Un Hide) the appropriate Rows. I'd suggest further that you Hide (or Un Hide) the entire Range at one time rather than iterating through the Cells...perhaps like this

Range(""D30:D77").EntireRow.Hidden = True

Re: Predict as Typing in Validation List

Hi Capitano

It'll do the job. Adapting it to your needs can be a bit squirrly...need help, let me know.

Re: Predict as Typing in Validation List

Hi Capitano

You can do as you describe by using a worksheet Combobox...see this link

http://www.contextures.com/xlDataVal10.html#AddCombo

Re: Combining 2 macros into 1

hi cartica

Were I you I'd not combine them...they're performing two distinct functions. I'd simply add a line of Code to PositionRec that Calls CreateSumif_Reference() like so

Sub PositionRec()
    Dim oWbK As Workbook, sh As Worksheet, fPATH As String, fNAME As String, i As Long
    Dim MSG As Long
    MSG = MsgBox("Proceed With Changes?", vbYesNo, "Proceed?")
     
     
    If MSG = vbYes Then
        MsgBox "Changes Saved!"
        With Sheets("As of Positions")
            .Range("A2:G200").ClearContents
        End With
        With Sheets("Open Trades")
            .Range("A2:O200").ClearContents
        End With
    Else
        MsgBox "Changes Not Saved"
        Application.Undo
    End If
     
    fPATH = "T:\StateStreet\Custody\Outgoing\AsOfPositions\"
    For i = 0 To 7
        fNAME = "AsofPositionsCartica" & Format(Date - i, "MMDDYY") & ".xls"
        If Len(Dir(fPATH & fNAME)) > 0 Then Exit For
        If i = 7 Then
            MsgBox "No files found dated in the past 7 days, aborting"
            Exit Sub
        End If
    Next i
    Set sh = ThisWorkbook.Sheets("As of Positions")
    Set oWbK = Workbooks.Open(fPATH & fNAME)
    Range("A2", Range("G" & Rows.Count).End(xlUp)).Copy sh.Range("A" & Rows.Count).End(xlUp).Offset(1)
    oWbK.Close False
     
    fPATH = "T:\StateStreet\Custody\Outgoing\OpenTrades\"
    For i = 0 To 7
        fNAME = "OpenTradesCartica" & Format(Date - i, "MMDDYY") & ".xls"
        If Len(Dir(fPATH & fNAME)) > 0 Then Exit For
        If i = 7 Then
            MsgBox "No files found dated in the past 7 days, aborting"
            Exit Sub
        End If
    Next i
    Set sh = ThisWorkbook.Sheets("Open Trades")
    Set oWbK = Workbooks.Open(fPATH & fNAME)
    Range("A2", Range("O" & Rows.Count).End(xlUp)).Copy sh.Range("A" & Rows.Count).End(xlUp).Offset(1)
    
    Call CreateSumif_Reference '<----Add this line of Code
     
End Sub

Re: Can a Worksheet_Change routine perform actions on a different worksheet?

Hi goneps

How about posting your Change Code...you say it's in the Sheet2 Code Module?

Re: Can a Worksheet_Change routine perform actions on a different worksheet?

Hi goneps

After the Copy/Paste try adding

Application.CutCopyMode = False

Re: Macro/method to scrub columns automatically from a daily report

Hi Steve

What's the Name of the Worksheet from which we're deleting these unwanted Columns?

Re: Macro/method to scrub columns automatically from a daily report

What version of Excel we be dealing with?