Likes Likes:  0
Page 1 of 5 123 ... LastLast
Results 1 to 10 of 42

Thread: Custom Function to find value and offset

  1. #1
    Board Regular
    Join Date
    May 2008
    Location
    Austria
    Posts
    950
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Custom Function to find value and offset

    Hi guys,
    I am in need for help.(
    I would like to write a custom function which findes certain values and offset once it is found two columns to the right.

    Code:
    function Test(strText as string) as string
    Select Case True
    Case strText = "Good"
    ?? offset found cell one column to the right get and modify that cell
    Case strText="Bad"
    ?? offset found cell one column to the right get and modify that cell
    Case strText ="Worst"
    ?? offset found cell one column to the right get and modify that cell
    Case else
    
    End select
    end function
    That is what I like to have but I am not able to get it to work...

    Many thanks for your helb

    Greetings

    Albert
    Silentwolf



    I use MS Office 2010 and Windows 7

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,293
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Custom Function to find value and offset

    Albert

    Where will you be using this function and where would it be looking for values?
    If posting code please use code tags.

  3. #3
    Board Regular
    Join Date
    May 2008
    Location
    Austria
    Posts
    950
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Custom Function to find value and offset

    Hi Norie,
    nice that you reply!!
    I am using this function within an array.. so I need a function to return values from it. offset from a cell which is found in a range of cells but I like to use it in an array rather then in a loop.
    Silentwolf



    I use MS Office 2010 and Windows 7

  4. #4
    Board Regular
    Join Date
    May 2008
    Location
    Austria
    Posts
    950
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Custom Function to find value and offset

    Norie,
    what I like to do is using a two dimensional array to calculate only values which are a certain value of the first dimension..
    Code:
    Sub GutschrifteninNeueTabelleSchreiben()
        Dim Guthaben() As Variant
        Dim r As Range
        Dim GuthabenCounter As Long
        Dim LoopCounter As Long
        
        For Each r In Range("A2", Range("A1").End(xlDown))
            If r.Offset(0, 8).Value = "SEPA-Gutschrift" Then
                GuthabenCounter = GuthabenCounter + 1
                
                ReDim Preserve Guthaben(1 To 10, 1 To GuthabenCounter)
                
                For LoopCounter = 1 To 10
                    Guthaben(LoopCounter, GuthabenCounter) = r.Offset(0, LoopCounter - 1).Value
                Next LoopCounter
            End If
        Next r
        
        Worksheets.Add
        
        Range(ActiveCell, ActiveCell.Offset(UBound(Guthaben, 2) - 1, 9)).Value = Application.Transpose(Guthaben)
    End Sub
    this code above gives me all found values and put it into a new worksheet.. got it from wise owl website

    But I like to do calculations so if the value is found then perform a calculation in the next column in this case is 0,9 and update this value in (0,9) with the new value..

    So if I have a custom function to gives me back a value I like to update values in Column("J") which are only found in Column("I") ...

    Hope that is a bit clearer what I like to do?

    As it depands on what values I have in column("I") I need to perform different calculations..

    Would be much appreciate if someone can give me a hand with this..

    Thank you!!
    Silentwolf



    I use MS Office 2010 and Windows 7

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,293
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Custom Function to find value and offset

    I think I understand, a bit anyway.

    Instead of this,
    Code:
    For LoopCounter = 1 To 10
         Guthaben(LoopCounter, GuthabenCounter) = r.Offset(0, LoopCounter - 1).Value
    Next LoopCounter
    you would loop to 9 and then pass the 9th value to a function to set the 10th.

    So something like this.
    Code:
    For LoopCounter = 1 To 9
         Guthaben(LoopCounter, GuthabenCounter) = r.Offset(0, LoopCounter - 1).Value
    Next LoopCounter
    
    Guthaben(10, GuthabenCounter) = TestFunction(9, GuthabenCounter))
    Is that correct or am I totally wrong?
    If posting code please use code tags.

  6. #6
    Board Regular
    Join Date
    May 2008
    Location
    Austria
    Posts
    950
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Custom Function to find value and offset

    Hi Norie,
    Yes loop through 9 and when it is found then calculate 10 and update it also in the 10th column..

    Hope you understand my explaination gg
    Silentwolf



    I use MS Office 2010 and Windows 7

  7. #7
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,293
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Custom Function to find value and offset

    That's kind of what the 2nd code I posted does, though I'm not sure about your function.
    If posting code please use code tags.

  8. #8
    Board Regular
    Join Date
    May 2008
    Location
    Austria
    Posts
    950
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Custom Function to find value and offset

    Ok so that would be the function or simelar to it..

    Code:
    Function BezahlteRechnungen(ByVal strText As String) As String
        Dim varWoerter As Variant
        Dim lngZaehler As Long
        
        varWoerter = Split(strText, " ")
        
        If IsNumeric(varWoerter(UBound(varWoerter))) Then
            BezahlteRechnungen = varWoerter(UBound(varWoerter))
        Else
            BezahlteRechnungen = strText
        End If
        
    End Function
    How would it look like then?
    Silentwolf



    I use MS Office 2010 and Windows 7

  9. #9
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,293
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Custom Function to find value and offset

    Like this I think.
    Code:
    For LoopCounter = 1 To 9
         Guthaben(LoopCounter, GuthabenCounter) = r.Offset(0, LoopCounter - 1).Value
    Next LoopCounter
    
    Guthaben(10, GuthabenCounter) = BezahlteRechnungen(9, GuthabenCounter))
    If posting code please use code tags.

  10. #10
    Board Regular
    Join Date
    May 2008
    Location
    Austria
    Posts
    950
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Custom Function to find value and offset

    hmm unfortunatelly there is a error wrong amount of arguments or wrong type of properties .. not sure I am using german version but along those lines..
    Silentwolf



    I use MS Office 2010 and Windows 7

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •