Can I Code a macro that has Saved Text descriptions based on a criteria?

[Trying2learnVBA]

Hello,

This is my second thread. I hope it goes as well as my first one in which I got all the help/tips I needed.

I am an accountant who loves excel and has come across vba to put my love for excel on steroids.
anyways - my boss heard I am good with excel macros (Lie - I am an amateur lol with common sense and google I've automated some long tedious reports - but he heard about it)

So he assigned me to automate a report he does.

I am stuck.

I need to have a pre saved description (a bunch really over 50)

I need something that IF say A1 ="xx" then C1 = "Pre set text description"
I have no idea how to go about doing this efficiently.


I could do something like - Copy A:A to sheet2. On sheet2 find replace. Then Copy Sheet2 A:A to Sheet1 C:C.

Any tips/help is greatly appreciated.

 

[DataBlake]

Sheet1 comes in this way:
[TABLE="width: 654"]
<tbody>[TR]
[TD="width: 117, bgcolor: transparent"]Bank[/TD]
[TD="width: 112, bgcolor: transparent, align: right"]8000052[/TD]
[TD="width: 104, bgcolor: transparent, align: right"]221475896[/TD]
[TD="width: 146, bgcolor: transparent, align: right"]43903[/TD]
[TD="width: 247, bgcolor: transparent"]FNMA P&I PAYMENTS[/TD]
[TD="width: 70, bgcolor: transparent, align: right"]0[/TD]
[TD="width: 75, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Bank[/TD]
[TD="bgcolor: transparent, align: right"]8000052[/TD]
[TD="bgcolor: transparent, align: right"]221475896[/TD]
[TD="bgcolor: transparent, align: right"]43903[/TD]
[TD="bgcolor: transparent"]FNMA P&I PAYMENTS[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]-19929.59[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Bank[/TD]
[TD="bgcolor: transparent, align: right"]131029[/TD]
[TD="bgcolor: transparent, align: right"]221475896[/TD]
[TD="bgcolor: transparent"]800-025[/TD]
[TD="bgcolor: transparent"]PAYMENTS CLEARING ACCOUNT[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]-633.16[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2 (I will create with all the cross references)
[TABLE="width: 288"]
<tbody>[TR]
[TD="width: 112, bgcolor: transparent, align: right"]8000052[/TD]
[TD="width: 112, bgcolor: transparent"]80000260000-1[/TD]
[TD="width: 160, bgcolor: transparent"]FNMA MTG PYMNTS[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]131029[/TD]
[TD="bgcolor: transparent"]6100010000-5[/TD]
[TD="bgcolor: transparent"]FNMA MTG SVC INCOME[/TD]
[/TR]
</tbody>[/TABLE]



This is what It want the end result to be. S1 should look like this
[TABLE="width: 454"]
<tbody>[TR]
[TD="width: 112, bgcolor: transparent"]Account Number[/TD]
[TD="width: 112, bgcolor: transparent"]GL[/TD]
[TD="width: 74, bgcolor: transparent"]Amount[/TD]
[TD="width: 306, bgcolor: transparent"]Description[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8000052[/TD]
[TD="bgcolor: transparent"]80000260000-1[/TD]
[TD="bgcolor: transparent, align: right"]-19929.59[/TD]
[TD="bgcolor: transparent"]FNMA MTG PYMNTS[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]131029[/TD]
[TD="bgcolor: transparent"]6100010000-5[/TD]
[TD="bgcolor: transparent, align: right"]-633.16[/TD]
[TD="bgcolor: transparent"]FNMA MTG SVC INCOME[/TD]
[/TR]
</tbody>[/TABLE]

it seems your instructions are different than your example of expected end result?
but i made this code and even commented on every line what it is doing to help you understand it better.
this will make the first 4 rows how you have it here

notes:
sheet 1 needs to be named .S2 as thats what you've been referring to it as (if not you can change the name in the code)
you will require headers although if you do not want headers just change ever i = 2 to i = 1

Sub test()
Dim ary1 As Variant
Dim ary2 As Variant
Dim lRow As Long
Dim i As Long
Dim x As Long


' this determines the last row of A
lRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = lRow To 1 Step -1
If Cells(i, 6) = 0 And Trim(Cells(i, 7)) = 0 Then
Rows(i).Delete
End If
Next i
lRow = Cells(Rows.Count, "A").End(xlUp).Row

' with activesheet means i don't have to declare which sheet i want to work in every time i write a line
With ActiveSheet

' these are our arrays i will be using them to alter our data. Think of them like a grid where ary(x, y) is ary(rows, columns)
' i've found that with large datasets this is the fastest approach
ary1 = .Range("A1").CurrentRegion.Value2
ary2 = Sheets(".S2").Range("A1").CurrentRegion.Value2

' this is saying from row 2 to the last row of the array1, i am assuming you have headers if you don't change 2 to 1
For i = 2 To UBound(ary1)
' this says to replace column 1 with column 2, column 3 with column 7, and adds a the lookup value to column 4
 ary1(i, 1) = ary1(i, 2)
 ary1(i, 3) = ary1(i, 7)
 ary1(i, 4) = ary1(i, 2)
Next i

'this pastes the array to the sheet
.Range("A1").Resize(UBound(ary1), 4).Value2 = ary1

'same thing, we are looping through the second array but do replace column B
For x = 2 To UBound(ary2)
 .Columns(2).Replace what:=ary2(x, 1), replacement:=ary2(x, 2), LookAt:=xlWhole
 .Columns(4).Replace what:=ary2(x, 1), replacement:=ary2(x, 3)
 Next x
 
End With

End Sub

let me know how that works for you.

 

[Trying2learnVBA]

Blakeskate
S1 Comes in this way and it can have a positive number on Col F Col G is Neg #s
A B C D E F G
[TABLE="width: 485"]
<colgroup><col width="64" style="width: 48pt;"> <col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;"> <col width="243" style="width: 182pt; mso-width-source: userset; mso-width-alt: 8886;"> <col width="64" style="width: 48pt;" span="4"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Bank[/TD]
[TD="width: 84, bgcolor: transparent, align: right"]8000052[/TD]
[TD="width: 243, bgcolor: transparent, align: right"]221475896[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]43903[/TD]
[TD="width: 64, bgcolor: transparent"]FNMA P&I PAYMENTS[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Bank[/TD]
[TD="bgcolor: transparent, align: right"]8000052[/TD]
[TD="bgcolor: transparent, align: right"]221475896[/TD]
[TD="bgcolor: transparent, align: right"]43903[/TD]
[TD="bgcolor: transparent"]FNMA P&I PAYMENTS[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Bank[/TD]
[TD="bgcolor: transparent, align: right"]8000052[/TD]
[TD="bgcolor: transparent, align: right"]221475896[/TD]
[TD="bgcolor: transparent, align: right"]43903[/TD]
[TD="bgcolor: transparent"]FNMA P&I PAYMENTS[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]-19929.6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Bank[/TD]
[TD="bgcolor: transparent, align: right"]70302410[/TD]
[TD="bgcolor: transparent, align: right"]221475896[/TD]
[TD="bgcolor: transparent"]800-025[/TD]
[TD="bgcolor: transparent"]PAYMENTS CLEARING ACCOUNT[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]-3693.16[/TD]
[/TR]
</tbody>[/TABLE]
So, I want to copy and paste this file I am calling S1 into my Macro workbook on S1!

On my Macro Workbook - I have a complete cross reference Guide which looks like this
[TABLE="width: 293"]
<colgroup><col width="64" style="width: 48pt;"> <col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;"> <col width="243" style="width: 182pt; mso-width-source: userset; mso-width-alt: 8886;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]8000052[/TD]
[TD="width: 84, bgcolor: transparent, align: right"]80000260000[/TD]
[TD="width: 243, bgcolor: transparent"]FNMA MTG PYMNTS [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]70302410[/TD]
[TD="bgcolor: transparent, align: right"]70400020000[/TD]
[TD="bgcolor: transparent"]PORTFOLIO 15YR PRINC PYMNTS [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7030011[/TD]
[TD="bgcolor: transparent, align: right"]70300010000[/TD]
[TD="bgcolor: transparent"]PORTFOLIO 20YR PRINC PYMNTS [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
</tbody>[/TABLE]
S2 Contains all possible account #s that I could get on the data I paste to S1.

The Final Result should be:
[TABLE="width: 333"]
<colgroup><col width="64" style="width: 48pt;"> <col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;" span="2"> <col width="212" style="width: 159pt; mso-width-source: userset; mso-width-alt: 7753;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]8000052[/TD]
[TD="width: 84, bgcolor: transparent, align: right"]80000260000[/TD]
[TD="width: 84, bgcolor: transparent, align: right"]-19929.59[/TD]
[TD="width: 212, bgcolor: transparent"]FNMA MTG PYMNTS [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7030010[/TD]
[TD="bgcolor: transparent, align: right"]70400020000[/TD]
[TD="bgcolor: transparent, align: right"]-3693.16[/TD]
[TD="bgcolor: transparent"]PORTFOLIO 15YR PRINC PYMNTS [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
</tbody>[/TABLE]

Your code seems to work once I figure out what to move around.

In words - I prep S1 by doing =F+G on C >copy C paste special to C. Then E:G.Delete.
I then run this code to delete rows if =0 on C
Dim iRow As Long
Dim iCntr As Long
iRow = 500
For iCntr = iRow To 1 Step -1
If Cells(iCntr, 3) = 0 Then
Rows(iCntr).Delete
End If
Next

Now I Can use your code to match S1 Col B to S2 Col A. If it matches bring data in S2 Col A, B & C to S1 Col A, B & D.
If it is easier the code can bring data to S1 Col A, B, C and I can use simple code to arrange it my way.
The main task is to match It to the proper references

 

[DataBlake]

it can have a positive number on Col F Col G is Neg #s

this should not matter
the most important thing you are leaving out is headers. its much easier to communicate to me what you want using headers.

So, I want to copy and paste this file I am calling S1 into my Macro workbook on S1!

On my Macro Workbook - I have a complete cross reference Guide which looks like this

okay so you also probably want to use whatever you're actually calling things so you don't confuse me, or even yourself.
there should be ONE workbook (xlsm)
S1 = Sheet 1
S2 = Sheet 2

sheet 1 is the csv file you import that looks EXACTLY like your first table with "bank" in column A before running any code, right?
sheet 2 is the "cross reference" sheet a.k.a whatever you want changed/matched in sheet 1.
make sure sheet 1 is selected as that is the sheet we are altering

here is the final result when you have .S1 and .S2 set the way you described in your post:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="align: right"]8000052[/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: right"]80000260000[/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: right"]-19929.6[/TD]

</tbody>

[/TD]
[TD]

FNMA MTG PYMNTS

<tbody>
</tbody>

[/TD]
[TD]

FNMA P&I PAYMENTS

<tbody>
</tbody>

[/TD]
[TD]

<tbody>
[TD="align: right"]0[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 61"]
<tbody>[TR]
[TD="width: 61, bgcolor: transparent, align: right"]-19929.6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="align: right"]70302410[/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: right"]70400020000[/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: right"]-3693.16[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD]PORTFOLIO 15YR PRINC PYMNTS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

PAYMENTS CLEARING ACCOUNT

<tbody>
</tbody>

[/TD]
[TD][TABLE="width: 14"]
<tbody>[TR]
[TD="width: 14, bgcolor: transparent, align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

<tbody>
[TD="align: right"]-3693.16[/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]





no prep required. no manual changes. no nothing. just paste the data in the sheets and run the code.
the reason you will see columns E-G is because we will clean up and delete everything we don't need after i ascertain that this is what you wanted.

you can change this line in the code

ary1(i, 3) = ary1(i, 7)

to

ary1(i, 3) =ary1(i, 6) + ary1(i, 7)

 

[Trying2learnVBA]

Yes this is very dead on.

Ok S1 (bank)

Lets call Col A "Acount" Col B "GL Col C "Amount" D "Description E-G can be deleted.

In one sentence - I need S1 To have Account-GL-Amount-Description. That's it. The only data that matters in S1 is account and amount. So Col B, F & G.

S2 has All possible Accounts in Col A. All Matching GLs in Col B and The Matching Description In Col C.

***Remember I need S1 Col C to keep any amount that is not "0" that is in either S1 Col F or Col G.

 

[DataBlake]

***Remember I need S1 Col C to keep any amount that is not "0" that is in either S1 Col F or Col G.

have you tried running my code?
it has the get rid of double 0's bit in there

or are you saying that if f+g = 0 delete that too?
i.e if you have 100 in f and -100 in g you want that to be removed?

otherwise it sounds like it covers everything you needed so this is what we have

Sub test()
Dim ary1 As Variant
Dim ary2 As Variant
Dim lRow As Long
Dim i As Long
Dim x As Long
Dim lastCol As Long

'this establishes the last column of your data based on your headers
lastCol = Range("A1").SpecialCells(xlCellTypeLastCell).Column

' this determines the last row of A
lRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = lRow To 1 Step -1
If Cells(i, 6) = 0 And Trim(Cells(i, 7)) = 0 Then
Rows(i).Delete
End If
Next i
lRow = Cells(Rows.Count, "A").End(xlUp).Row

' with activesheet means i don't have to declare which sheet i want to work in every time i write a line
With ActiveSheet

' these are our arrays i will be using them to alter our data. Think of them like a grid where ary(x, y) is ary(rows, columns)
' i've found that with large datasets this is the fastest approach
ary1 = .Range("A1").CurrentRegion.Value2
ary2 = Sheets(".S2").Range("A1").CurrentRegion.Value2

' this is saying from row 2 to the last row of the array1, i am assuming you have headers if you don't change 2 to 1
For i = 2 To UBound(ary1)
' this says to replace column 1 with column 2, column 3 with column 7, and adds a the lookup value to column 4
 ary1(i, 1) = ary1(i, 2)
 ary1(i, 3) = ary1(i, 6) + ary1(i, 7)
 ary1(i, 4) = ary1(i, 2)
Next i

'this pastes the array to the sheet
.Range("A1").Resize(UBound(ary1), 4).Value2 = ary1

'same thing, we are looping through the second array but do replace column B
For x = 2 To UBound(ary2)
 .Columns(2).Replace what:=ary2(x, 1), replacement:=ary2(x, 2), LookAt:=xlWhole
 .Columns(4).Replace what:=ary2(x, 1), replacement:=ary2(x, 3)
 Next x
 
' this deletes any data beyond column E
Range("E1", Cells(1, lastCol)).EntireColumn.Delete Shift:=xlToLeft
 
End With

End Sub

 

[Trying2learnVBA]

Blake - there is never such scenario of 100 -100. So we are covered.

When I run it - I get the debugger which I don't fully know how to use. This is likely to naming errors. I will thru areas that call for S1 or S2.
My workbook has "1" and "2" as the name for the sheets.

 

[Trying2learnVBA]

Wow!! Your code works great!!!
The issue was simple. Change S2 to "2"

This is fun learning.

My solution is so rookie but I feel great that I got it done using common sense and tons of line by line code!

Your way is just so much easier for any user to modify. My method requires opening the editor. People would be intimidated by it likely

 

[DataBlake]

Wow!! Your code works great!!!
The issue was simple. Change S2 to "2"

This is fun learning.

hopefully the comments help. (which i put above the line im referring to)
if you need me to explain any of the methods used so you can implement it in further code just let me know.
also you probably don't want to name your sheets plain numbers. it will get confusing because you can reference a sheet by the order from left to right

Worksheets(2)

and then your worksheet that is named "2"

Worksheets("2")

and that will get confusing down the road

 

[Trying2learnVBA]

Very good tip with the simple name "2"

I will give S1 and S2 much better names