Posts by RichardSchollar

    Re: Match Function On Range, Not Row Or Col


    Hi


    Match only works with single column/single row ranges (as you have found). What would you like a formula like:


    =MATCH("Last",A1:HE1000,0)


    to return? Are you expecting a range reference eg like Z89 returned, or something else?


    Richard

    Re: Runtime Error 91 With Find() Method


    Hi


    You'll receive this error if the serached for term is not found in the being-searched range. Also, unless Sheet2 is active at the time you execute the statement, you will get a 1004 error as you can only activate cells on the currently active sheet.


    when using Find, you should always assign the results of Find to a range variable and then test whther or not anything was found - this way you can avoide errors:


    Code
    Dim r As Range
    Set r = Sheet2.Range("F:F").Find(Sheet3.Cells(i,1))    'note NO activate!!
    If Not r Is Nothing Then  'if it isn't nothing then it is something ie it was found!
      r.Activate
    Else
      Msgbox "Not found!"   'or whatever you deem appropriate
    End If


    Richard

    Re: Formulas To Value On A Filtered Range With Macro


    Hi


    try looping thru the Visible cells rather than just formulas (then you can determine whether cell has a formula or not and act accordingly) eg something like:


    Code
    For Each r In ActiveSheet.Autofilter.Range.SpecialCells(xlCellTypeVisible)
      If r.HasFormula Then r.Value = r.Value
    Next r


    Richard

    Re: Add X Decimal Places to Numbers But Not Zeros


    But surely that's a question of how it is displayed, which is achievable thru custom formatting. Perhaps you could look at the precision as displayed feature under Tools>Options>Calculation tab (note this is a dangerous option to implement as you will permanently lose original data), but I'm not sure that even this will show 0.7 as 0.70 for the reason that Excel sees these two values as being identical.


    Richard

    Re: Protect Sheet Allowing A Macro To Modify


    Setting UserInterfaceOnly to True only applies to the current Excel session - it resets to False on closing the workbook. So you would need to reapply every time you opened the workbook (and wanted to run code on the worksheet). Something to be aware of.


    Richard

    Re: Counting Characters Per Line


    Assuming you want the lengths of each line and each line is delimited by a LineFeed character, then the following entered in B1 and copied across columns (ie to F1) will give the lengths of the lines of the text contained in cell A1. Note that a LineFeed character ( that produces the new line) counts as 1 character):


    =LEN(MID($A1,SUMPRODUCT(N(OFFSET($A1,0,COLUMN($A1:A1)-COLUMN($A$1))))+1,FIND(CHAR(10),$A1&CHAR(10),SUMPRODUCT(N(OFFSET($A1,0,COLUMN($A1:A1)-COLUMN($A$1))))+1)-SUMPRODUCT(N(OFFSET($A1,0,COLUMN($A1:A1)-COLUMN($A$1))))))


    Richard

    Re: Error 1004 On Vba Range


    The first thing that jumps out at me is that you don't have fully qualified range references on your sheet. This is because if you have the following line:


    Code
    ThisWorkbook. Sheets("Destination Info"). Range("B2", Range("B2").End(xlDown)).Clear


    then the Range("D2") within the first Range() actually refers to Range("D2") on the Activesheet which is not "Destination Info". To properly qualify the sheet reference, you need to write it as:


    Code
    With ThisWorkbook.Sheets("Destination Info")
      .Range("B2", .Range("B2").End(xlDown)).Clear
    End With


    Note the addition of the period (.) before the Range (within the other Range).


    You need to replace all such instances of unqualified range references with the above construct.


    Best regards


    Richard

    Re: Add X Days To Date, Excluding Weekends & Holidays In Macro


    Hi


    Sounds to me as if the WORKDAY formula would be a better choice for you. You can use Evaluate to access these, but I would personally set a reference to the Analysis Toolpak in your VBA Project (ie via Tools>References within the VBE).


    The file you need to reference to is:


    atpvbaen.xks


    Then you can use the functions directly eg:


    Code
    x = Workday(Date,+2,Holidays)


    Richard

    Re: Set Chart Title Equal To A Defined String


    Hi


    Title as defined is a scalar string variable - not an object, therefore you don't use the Set keyword when assigning a value. Something like the following should work:


    Code
    Dim strTitle As String
    strTitle = Sheet1.Cells(2,93) & "/" & Sheet1!.Cells(2,94) & "/" & "Primary's"
    With Activechart
      .HasTitle = True
      .ChartTitle.Text = strTitle
    End With


    Richard