Well versed in formulas but NOT VBA Code - Help a noob!

[neongreenn]

Good afternoon - first time poster here.

I need some VBA help with a code. I've already developed a formula that does what I need it to in 2 separate columns other than the 3 listed below. So ultimately, if this isn't possible, the formula will suffice just fine. There is an example of the spreadsheet I'm working on below. What I would like to do is develop a Macro that will do the same thing the formula does except in the columns where the data already exists.

If the Description contains 1 of 4 things (******776, ******732, Vanguard, or Robinhood) AND the Type is a Withdrawal, I want the value under Amount to go from a negative value to a positive value and I want the Type to go from Withdrawal to Deposit. The number will only ever need to go from negative to positive. The reason these need to be If/AND type statements is because even though the row contains the key word Robinhood, there are both withdrawals and deposits that contain the same word. So I wouldn't want to make those positive numbers negative by mistake.

I have a considerable amount of Excel Formula knowledge but I am DEFINITELY a VBA noob BUT I took a stab at the VBA code and it did change the values of the Amount column that contained the specifics above, but it changed them all to 0. I also couldn't get the word Withdrawal to say Deposit.

Again, below is an example of the data table I'm working with, I will also paste the excel formulas that I used (that do work for the purpose I need them for) as well as (AS LONG as you promise not to laugh) my stab at the VBA code. I really think I'm still looking at VBA and thinking in excel formula mode. SO any help would be greatly appreciated!!! Thank you in advance for your help!

Excel Formulas: =IF(OR(AND(ISNUMBER(SEARCH("******776",C2)),B2="Withdrawal"), (AND(ISNUMBER(SEARCH("******732",C2)),B2="Withdrawal")), (AND(ISNUMBER(SEARCH("Vanguard",C2)),B2="Withdrawal")), (AND(ISNUMBER(SEARCH("Robinhood",C2)),B2="Withdrawal"))),"Deposit",B2)
Excel Formulas: =IF(AND(D2="Deposit",A2<0),A2*-1,A2)
VBA Code Attempt:

Sub Macro1()
    Dim N As Long, i As Long

    N = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 1 To N
        v1 = Cells(i, 1).Value
        v2 = Cells(i, 2).Value
        v3 = Cells(i, 3).Value

        If v1 <= 0 And v2 = Cells.Find(What:="Withdrawal") _
And v3 = Cells.Find(What:="******776") Then 
Cells(i, 1).Value = Number * 1 
Cells(i, 3).Value = "Deposit"
End If

        If v1 <= 0 And v2 = Cells.Find(What:="Withdrawal") _
And v3 = Cells.Find(What:="******732") Then 
Cells(i, 1).Value = Number * 1 _
Cells(i, 3).Value = "Deposit"
End If

        If v1 <= 0 And v2 = Cells.Find(What:="Withdrawal") _
And v3 = Cells.Find(What:="Vanguard") Then _
Cells(i, 1).Value = Number * 1 And Cells(i, 3).Value = "Deposit"
End If

        If v1 <= 0 And v2 = Cells.Find(What:="Withdrawal") _
And v3 = Cells.Find(What:="Robinhood") Then 
Cells(i, 1).Value = Number * 1 
Cells(i, 3).Value = "Deposit"
End If

    Next i
End Sub

Amount	Type	Description
1.2	Deposit	GM
0.7	Deposit	Aerotek
-0.3	Withdrawal	Clark
-1.99	Withdrawal	Sunoco
4.1	Deposit	Aerotek
0.24	Deposit	Interest
-2.13	Withdrawal	Sunoco
-1	Withdrawal	******776
-1	Withdrawal	******732
-2	Withdrawal	Discover
1.23	Deposit	Aerotek
1.13	Deposit	GM
-3.78	Withdrawal	Sunoco
5.01	Deposit	Chster
0.99	Deposit	Robinhood
3.25	Deposit	Aerotek
-6.12	Withdrawal	HSBC
-2	Withdrawal	Sunoco
-6	Withdrawal	Vanguard
-1	Withdrawal	Check Paid
-5	Withdrawal	Chin Lane
5	Deposit	Aerotek
-3	Withdrawal	Clark
-0.12	Withdrawal	Robinhood

[Paul_Hossler]

I agree - I think you were approaching the problem from the WS POV

From a VBA approach, maybe something like this.

Option Explicit
'If the Description contains 1 of 4 things (******776, ******732, Vanguard, or Robinhood) AND the Type is a Withdrawal,
'I want the value under Amount to go from a negative value to a positive value and
'I want the Type to go from Withdrawal to Deposit. The number will only ever need to go from negative to positive.
'The reason these need to be If/AND type statements is because even though the row contains the key word Robinhood,
'there are both withdrawals and deposits that contain the same word.
'So I wouldn't want to make those positive numbers negative by mistake.

Const colAmt As Long = 1
Const colType As Long = 2
Const colDesc As Long = 3

Sub ManipulateData()
    Dim rData As Range, rRow As Range
    
    Set rData = ActiveSheet.Cells(1, 1).CurrentRegion
    
    Application.ScreenUpdating = False
    
    For Each rRow In rData.Rows
        With rRow
            Select Case .Cells(colDesc).Value
                Case "******776", "******732", "Vanguard", "Robinhood"
                    If .Cells(colType).Value = "Withdrawal" Then
                        .Cells(colAmt).Value = -1# * .Cells(colAmt).Value
                        .Cells(colType).Value = "Deposit"                                    '!!!! missed this
                    End If
            End Select
        End With
    Next
    Application.ScreenUpdating = True
End Sub

[SamT]

I started to just add CODE tags, White space and general 'paragraph' formatting to your code, but I got carried away and removed those "Ands" between value assignments. My bad

There are still some logic errors that need correcting.

Have you ever heard of a positive withdrawal? Or a negative deposit? So we don't have to check for Negative AND Withdrawal, just one or the other.


Start with this and see what you can do

Const v1 = "Vanguard"
Const V2 = "RobingTheHood"
Const v3 = ?
Const V4 = ?

If Cells(i,1) < 0 And(Cells(i, 3) = V1 Or Cells(i, 3) = V2 Or Cells(i, 3) = V3 Or Cells(i, 3) =V4) Then
Cells(i, 1).Value = Abs(Cells(i, 1)
Cells(i, 2) = ?
End If 
Next

This is a tiny bit faster

Const Tester As String = "Vanguard, Robinhood, ******776, ******732"
'
'
... And InStr(Tester, Cells(i, 3)) > 0 Then