Posts by RichardSchollar

  • Nest Match & Index Functions

    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

  • Find & Find Next For All Occurences/Instances

    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

  • Convert Formulas In Filtered Range To Values

    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

  • Add X Decimal Places to Numbers But Not Zeros

    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

  • Protect Sheet But Allow Macro Code To Modify

    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

  • Count Characters In Cell Per Line Break

    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

  • Clear Range, Copy Another & Paste Values Then Advanced Filter

    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

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

    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

  • Set Chart Title Using Cells Via Macro Code

    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