VBA Macro Searching for specific text

[Saeidian]

Hello everyone,

I am trying to convert below formula to VBA Macro in excel and I was wondering if anybody can help me with that. I initially started but I can not put all condition into the code because my VBA knowledge is limited. Would you please help me?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thank you so much for your help in advance!

[FDibbins]

Hi, welcome to the forum

What exactly are you trying to do here, and why do you need VBA for that?

[Saeidian]

Good Morning Ford,

I am trying to tag the transactions type by searching through transaction description in column "D" for transfer between accounts. If the description in column "D" has word "Transfer" I want to have word "Transfer" to show in my column "B" of my worksheet. If it sees word "Transfer" along with "Fee" in the description, I want to tag the transaction for word "Debit" in column "B" of my worksheet and finally if non of that possibilities, then looks at my amount in column "I" if the number is negative, tag my transaction with "Debit" in column "B" and if my amount in column "I" is positive, tag my transaction with word "Credit" in column "B".

Thank you for your help!

Regards,

Saeidian

[Saeidian]

Good Afternoon Ford,

Here is what I am trying to do! Would you please look at this when you have a minute and tell me if you could help me with it?

Basically this formula is doing the following.

1- Searches column D cell “D2” for word “Transfer” not cap sensitive. (TRANSFER, Transfer, transfer)
2- If it finds word “Transfer” it will enter word “Transfer” in column C cell “C2” of my worksheet.
3- If it finds word “Transfer” in conjunction with word “Fee” it will enter word “Check” in column “C2” of my worksheet.
4- If there is no word “Transfer” and there is no word “Transfer” along with word “Fee” to be found in my column “D” cell D2 then
5- It looks at my column “I” cell I2, if “I2” is below zero then it enters word “Check” in “C2” if “I2” greater that zero it will enter word “Deposit” in cell “C2”
6- It will test every cell in column D and return the same result
7- The search stops If cells in column “I” is blank

[FDibbins]

My VBA is weak, but Im sure if you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like, 1 of the other members will pick this up and help you

[Saeidian]

Thank you Ford! I have attached a excel sheet with two taps one before and one after. Hope someone could help me!

[jindon]

See B7 in result is different from yours.

Please Login or Register  to view this content.

[Saeidian]

Hello Jindon,

I really appreciate your help! That works perfect! It does the job perfectly. The only thing is that I wanted the result in column "C" instead of column "B". I thought I could change the code by myself and alter it but it seems that I am unable to do it.

Best Regards,

Saeidian

[jindon]

If sheet layouts are different, no body can answer.

[Saeidian]

Hello,

I am really sorry to bother you. Everything stays the same way, except data will populate in column C instead. Please see attached sample file.
It shifts on column to the right.

Regards,

Saeidian

[jindon]

Change to

Please Login or Register  to view this content.

[sanram]

You can use this code also :

Please Login or Register  to view this content.

[Saeidian]

Thank you so much to both of you! Wow you guys are amazing!

[sanram]

You are most welcomed.

[jindon]

If that takes care of your original question, select Thread Tools from the menu link above and mark this thread as SOLVED.

[bebo021999]

Another code without Worksheet Formula coding:

Sub transaction() 
Dim TemArr() 
Dim DesArr As Range 
Dim i, Find As Long 
Set DesArr = Range("D2:J" & [J2].End(xlDown).Row) 
ReDim TemArr(1 To DesArr.Rows.Count, 1 To 1) 
    For i = 1 To UBound(TemArr) 
        Find = InStr(1, DesArr.Cells(i, 1), "transfer", vbTextCompare) 
            Select Case Find 
                Case Is > 0 
                    If (InStr(1, Space(1) & DesArr.Cells(i, 1) & Space(1), Space(1) & "fee" & Space(1), vbTextCompare) > 0) Or _ 
                    (InStr(1, Space(1) & DesArr.Cells(i, 1) & Space(1), Space(1) & "fees" & Space(1), vbTextCompare) > 0) Then 
                        TemArr(i, 1) = "Check" 
                    Else: 
                        TemArr(i, 1) = "Transfer" 
                    End If 
                Case Is = 0 
                    If DesArr(i, 7) < 0 Then 
                       TemArr(i, 1) = "Check" 
                    Else: 
                       TemArr(i, 1) = "Deposit" 
                    End If 
            End Select 
    Next i 
    [C2].Resize(UBound(TemArr), 1) = TemArr 
End Sub  

[Saeidian]

Hello,

you helped me last year with below vba code and it works for me perfectly. However, I would like to through another condition to the following coding, which it recognizes "Overdraft Protection" in the memo as Transfer as well in addition to this statement If a(i, 1) Like "*transfer*". I tried to add another if statement like " if a(I, 1) Like "*Overdraft Protection*" but I receive compiling error.
Would you please advice?

I really appreciate your help in advance.

Please Login or Register  to view this content.

[jindon]

If you get compiling error, that's because you didn't change the code properly.
That's all I can say.

[Saeidian]

Thank you Jindon.

[Saeidian]

Hello Jindon,

I played around with this all this morning and It does not give me the compile error anymore which is great but it recognizes the "OVERDRAFT PROTECTION" not "transfer". In essence insets word transfer for transaction that includes "OVERDRAFT PROTECTION" but inserts word Check or Deposit instead of Transfer for transactions that have transfer. Any suggestions would be GREATLY APPRECIATED!

Please Login or Register  to view this content.

[jindon]

Then I need to see your file with data/result.

[Saeidian]

Hello Jindon, Please see attached sample.

Thank you again for looking to this .

[jindon]

You mean

Please Login or Register  to view this content.

?

[Saeidian]

Yes, it works now. Thank you so much for your assistant. Have a great weekend.