PDA

View Full Version : Well versed in formulas but NOT VBA Code - Help a noob!



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

Paul_Hossler
08-17-2016, 12:52 PM
I agree - I think you were approaching the problem from the WS POV :devil2:

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
08-17-2016, 12:58 PM
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