Custom Function to find value and offset

[silentwolf]

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.

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

 

[Norie]

If you only want to update 1 column why does the array hold data for 10 columns?

 

[silentwolf]

Notrie,

The Column("J") is "Umsatztext" and in Column "Buchungstext" is the "SEPA-Lastschrift" ... but of course in "Buchungstext" there are others as well such as "SEPA-Gutschrift" eg... but as I need different calculations depanding on
what it is I need the function to perform only if "SEPA-Lastschrift" is found then use that function.. If you are finished with the function put it back into the cell where it was found and updated..

It needs to be like that .. not in a new worksheet.

 

[silentwolf]

It could als just hold two as "Buchungstext" and "Umsatztext"
Buchungstext to find what I am looking for and Umsatztext to change

 

[Norie]

Albert

I didn't add anything for a new workbook, it's in the original code, here:

Worksheets.Add
    
Range(ActiveCell, ActiveCell.Offset(UBound(Guthaben, 2) - 1, 9)).Value = Application.Transpose(Guthaben)

 

[silentwolf]

Norie,

sorry yes that is a different thing it was the code where I started out trying to work it out.
But I dont need a new worksheet or workbook I need the data in the same workbook worksheet .. in column ("J") updated after it runs through the custom function.

Please can you tell me how??

 

[silentwolf]

IBAN	Auszugsnummer	Buchungsdatum	Valutadatum	Umsatzzeit	Zahlungsreferenz	Waehrung	Betrag	Buchungstext	Umsatztext
XXXX	1	04.01.2016	04.01.2016	2015-12-31-20.57.38.150143		EUR	0	SEPA-Gutschrift	Anna Maria Reitstätter Bichlachweg 4 6385 Schwendt
XXXX	1	04.01.2016	04.01.2016	2016-01-04-12.40.46.80754		EUR	0	SEPA-Lastschrift	VB Leasing Finanzierung Quellenstrasse 51 - 55 Wien
XXXX	1	04.01.2016	04.01.2016	2016-01-04-12.40.46.734133		EUR	0	SEPA-Lastschrift	Allianz Elementar Versicherungs-Aktiengesellschaft Hietzinger Kai 101-105 1130 Wien
XXXX	1	04.01.2016	04.01.2016	2016-01-04-12.40.46.668468		EUR	0	SEPA-Lastschrift	Wüstenrot Versicherungs-AG
XXXX	1	15.01.2016	15.01.2016	2016-01-15-07.53.04.182978		EUR	-4,9	SEPA-Lastschrift	UPC BUSINESS AUSTRIA GMBH TF 222 EUR WOLFGANGGASSE 58-60 1120

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

The above is what I got

the below is what I want...

IBAN	Auszugsnummer	Buchungsdatum	Valutadatum	Umsatzzeit	Zahlungsreferenz	Waehrung	Betrag	Buchungstext	Umsatztext
XXXX	1	04.01.2016	04.01.2016	2015-12-31-20.57.38.150143		EUR	0	SEPA-Gutschrift	Anna Maria Reitstätter Bichlachweg 4 6385 Schwendt
XXXX	1	04.01.2016	04.01.2016	2016-01-04-12.40.46.80754		EUR	0	SEPA-Lastschrift	VB Leasing Finanzierung
XXXX	1	04.01.2016	04.01.2016	2016-01-04-12.40.46.734133		EUR	0	SEPA-Lastschrift	Allianz Elementar Versicherungs-Aktiengesellschaft
XXXX	1	04.01.2016	04.01.2016	2016-01-04-12.40.46.668468		EUR	0	SEPA-Lastschrift	Wüstenrot Versicherungs-AG
XXXX	1	15.01.2016	15.01.2016	2016-01-15-07.53.04.182978		EUR	-4,9	SEPA-Lastschrift	UPC BUSINESS AUSTRIA GMBH

<tbody>
</tbody>

in the same worksheet-- no transpose or add new worksheet all withouth just back into the same location as it was.

I am sure it is possible )

 

[Norie]

Albert

Couldn't you just use the function as a UDF on the sheet?

For example you could put this in a spare column and copy it down.

=IF(I2="SEPA-Lastschrift", LastschriftenTextUpdate(J2) ,J2)

 

[silentwolf]

Norie,
It is part of a bigger VBA project and I really would like it as I attent do to it ..
There is a way that it can be done I am sure of if.. I had it already so far that it got it next to the cell I wanted it.. but that is not good I really would need to column be updatet afer I run the Prozedur .(

 

[Norie]

Albert

I understand that and my suggestion of using a formula is only something to make sure the function actually works.

 

[silentwolf]

Norie,
the function works .-) I did already managed that I get the result in the next column but not in the same cloumn .( sad... and I tried so many different things and cant not get to how I am able to get it to work..