Posts by lecxe

    Re: Placing Vba Objects In Worksheet


    Hi googlebot


    Bob is telling you how to insert an ActiveX Textbox.


    From what I understood you want to insert the Textbox that you used to access in the Design Toolbar in previous versions. That one you can access in xl2007 in the Insert tab, in the Text group.


    HTH
    lecxe

    Re: Save Diagram As Gif Image


    Hi maxald
    Welcome to the board


    Check the help on the method Export of the Chart object.


    This is the example in the help:


    Code
    Worksheets("Sheet1").ChartObjects(1) _
            .Chart.Export _
            FileName:="current_sales.gif", FilterName:="GIF"

    Re: Maco Functions For Parsing Sub-Strings


    Hi pike


    Replace the pattern in Jindon's code by:


    Code
    .Pattern = "([A-Z]+)\D*(\d{1,2}:\d{1,2}:\d{1,2}).*"


    And then use Jindon's instructions:
    - Select B1:C1
    - Enter =rapha(A1) in the formula bar
    - Then confirm with Ctrl + Shift + Enter (array formula entry)


    HTH
    lecxe

    Re: Extract Specific Numbers From Text Strings


    Another one, equivalent to Jindon's:


    Code
    Function GetNumbers(s As String, i As Integer) as String
        With CreateObject("vbscript.regexp")
            .Pattern = "\D+|(" & i & "\d*)?\d*"
            .Global = True
            GetNumbers = Replace(Application.Trim(.Replace(s, "$1 ")), " ", ",")
        End With
    End Function


    Use it like this:
    =GetNumbers(A1,2)


    HTH
    lecxe

    Re: Retrieve Chart Field Values


    1 - You cannot write the series formula directly in the cell as a formula because excel tries to interpret it as a worksheet formula, and there's no worksheet formula Series().


    You have to write the formula as text. You either format the cell as text before writing the series formula, or you use the text prefix "'".


    Try:


    Code
    Analysis Charts List.xls").Worksheets("Range by Chart").Range("write_range") = "'" &  .SeriesCollection(1).Formula


    2 - to write the appropriate number of series formulas, use the SeriesCollection .Count property, like


    Code
    Dim lSeries as Long
    
    
    For lSeries=1 to .SeriesCollection.Count
       ....
        ... = "'" & .SeriesCollection(lSeries).Formula
    Next l


    Remark: There's one thing I don't understand in your code, why do you write 2 times in the same cell, Range("write_range") ??? The second time you overwrite the existing data, no? Please check it.


    HTH
    lecxe


    Edit: in the first code box I noticed that the browser added a space before the single quote. I'ts "'" (double quote, single quote, double quote), no space.

    Re: Retrieve Chart Field Values


    Hi Tai


    The Values and XValues properties only give you the actual values and not their source. You'll be better off if you print the series formulas.



    In case you're not familiar with it, a series formula has usually this syntax


    =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$1624,Sheet1!$B$2:$B$1624,1)


    and the parameters are: Name, Category (X axis), Values (Y values), (series) Order.



    I believe you have all the information you need in the series formula.


    In your case you'd write .SeriesCollection(1).Formula in your code.


    If you think this solves your problem and have problems with the implementation post back with details.


    Kind regards
    lecxe

    Re: Function To Write An Array


    I agree with you, in this case it makes more sense to use a Sub. I was merely pointing out the fact that vba functions running in vba context can alter the environment. Like you said, however, in this case he's not returning any value and so a Sub makes more sense.


    kind regards
    lecxe

    Re: Function To Write An Array


    Hi Mike


    Quote

    Functions return values, they don't write to cells.


    I think you are talking about UDFs (context worksheet) and not vba functions (context vba). If you call a UDF from a formula in a worksheet you have restrictions that prevent you from changing the environment, like writing to cells, hiding columns, etc. Here, however, that's not the case. The function is called from a Sub, we are in vba context, and so the vba function has no restrictions, it's just vba code.


    This doesn't mean that I don't agree that it makes more sense to use a Sub in this case, since you are not returning a result.


    Kind regards
    lecxe

    Re: Text- To Rows ( In 1 Column)


    Hi wollyka


    here's another option:


    Re: Validate X Only Has All Text Or All Numbers, Not Both


    Similar UDF's but using the Like operator, instead of RegEx's:



    Re: Minimum/Maximum Associated Within Rate Range


    Hi nbostic


    All the questions seem to be variations on the same theme, so this is the solution to the first one and you can adapt it to the other questions.


    Assuming:


    - Date in column A
    - Price in column B
    - You want to know the smallest non-zero price for the year 2007


    Try:


    =MIN(IF(YEAR(A2:A100)=2007,IF(B2:B100>0,B2:B100)))
    confirmed with CTRL-SHIFT-ENTER


    Adjust the ranges.


    Hope this helps
    lecxe

    Re: Reduce/Refine Row Number String Addresses


    Mike: I tried it but the string didn't change


    shg: Great! I didn't know that intersect would combine contiguous ranges.
    I also tried it with .Union with success.


    A new one I learnt today. Thanks.

    Re: Filter By Multiple Conditions/Criteria


    Hi jonny


    3 remarks


    - a regular expression is a big gun for a simple pattern comparison like this


    - I think you forgot something before the "7" in you example. Didn't you mean "ABC1234XYZ7"? Else, the pattern has one "\D" too much.


    - Looking at your example the pattern you chose doesn't seem adequate. It accepts "AB,1234!%Z7". Is this really what you want? Don't you mean just digits and letters?



    Solution 1


    This is an example of a function that tests if a string matches the pattern.


    I assumed this pattern:


    3 letters then 4 digits then 3 letters then 1 digit then, optionally, some more digits


    Code
    Function TestS(s As String) As Boolean
    If Len(s) > 10 Then _
        TestS = s Like "[A-Z][A-Z][A-Z]####[A-Z][A-Z][A-Z]" & String(Len(s) - 10, "#")
    End Function


    If you want to have a case insensitive comparison include as the first line in the module:
    Option Compare Text



    Solution 2


    If this was just an example and you do need regular expressions, for more complex patterns, this is an example with the same assumptions for the pattern:


    Code
    Function TestS(s As String) As Boolean
    
    
    With CreateObject("vbscript.regexp")
        .Pattern = "^[A-Z]{3}\d{4}[A-Z]{3}\d+$"
        .IgnoreCase = True ' or don't include this statement
        TestS = .Test(s)
    End With
    End Function


    HTH
    lecxe

    Re: Reduce/Refine Row Number String Addresses


    Hi Eric


    Try:


    Code
    Function CombineRange(s As String) As String
    Dim vS As Variant, l As Long
    
    
    vS = Split(s, ":")
    For l = 0 To UBound(vS) - 1
        If Evaluate(Replace(vS(l), ",", "-")) = -1 Then vS(l) = ""
    Next l
    CombineRange = Replace(Application.Trim(Join(vS)), " ", ":")
    End Function


    HTH
    lecxe

    Re: Multiply & Add Cells In Range With Set Cells


    Hi Eddiie


    You can for ex. use the worksheet function Sumproduct().


    Adapt this example:


    Re: Subtracting Given Values From Number


    Hi Risto85


    Assuming a layout similar to your example with:


    - Headers in Row 1 starting in column A
    - Stock in column B
    - Forecast for April-December in columns C:K


    then:


    Number of months covered in N2:


    =IF($B2>=SUM($C2:$K2),"OK",SUMPRODUCT(--(SUBTOTAL(9,OFFSET($C2,0,0,1,COLUMN($C2:$K2)-COLUMN($C2)+1))<=$B2)))


    Out of stock month in M2:


    =IF(N2="OK","OK",INDEX($C$1:$K$1,N2+1))


    "OK" means you have enough stock for the all months forecasted.


    HTH
    lecxe[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Hi again


    Quote

    Hi,


    Thanks for your quick relpy. I seem to have some sort of problem getting this formula to work, do you have idea what could be wrong?


    http://img214.imageshack.us/my.php?image=excelvg5.jpg


    I've just tried the solution on a newly created workbook and it worked.


    I'm sorry but I don't see what can be the problem.
    In some contries the parameters of a function are separed by a semicolon ";" instead of by a comma as in the English version.


    Using the formula in N2 I get the value 2 which seems correct. The total of the first 2 months is 2100, still covered by the stock. The total of the first 3 months is 6100, no longer covered by the stock.


    Maybe someone else can try it and give feedback?