neongreenn
08-17-2016, 10:30 AM
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
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