Custom Function to find value and offset

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,216
Office Version
  1. 2016
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Albert

Where will you be using this function and where would it be looking for values?
 
Upvote 0
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.
 
Upvote 0
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!!
 
Upvote 0
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?
 
Upvote 0
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 :)
 
Upvote 0
That's kind of what the 2nd code I posted does, though I'm not sure about your function.
 
Upvote 0
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?
 
Upvote 0
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))
 
Upvote 0
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..
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,720
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top