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

Trying2learnVBA

Board Regular
Joined
Aug 21, 2019
Messages
67
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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.
 
Sheet1 comes in this way:
Bank800005222147589643903FNMA P&I PAYMENTS00
Bank800005222147589643903FNMA P&I PAYMENTS0-19929.59
Bank131029221475896800-025PAYMENTS CLEARING ACCOUNT0-633.16

<tbody>
</tbody>

Sheet2 (I will create with all the cross references)
800005280000260000-1FNMA MTG PYMNTS
1310296100010000-5FNMA MTG SVC INCOME

<tbody>
</tbody>



This is what It want the end result to be. S1 should look like this
Account NumberGLAmountDescription
800005280000260000-1-19929.59FNMA MTG PYMNTS
1310296100010000-5-633.16FNMA MTG SVC INCOME

<tbody>
</tbody>

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

Code:
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.
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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
Bank800005222147589643903FNMA P&I PAYMENTS00
Bank800005222147589643903FNMA P&I PAYMENTS00
Bank800005222147589643903FNMA P&I PAYMENTS0-19929.6
Bank70302410221475896800-025PAYMENTS CLEARING ACCOUNT0-3693.16
<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> </tbody>
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
800005280000260000FNMA MTG PYMNTS
7030241070400020000PORTFOLIO 15YR PRINC PYMNTS
703001170300010000PORTFOLIO 20YR PRINC PYMNTS

<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> </tbody>
S2 Contains all possible account #s that I could get on the data I paste to S1.

The Final Result should be:
800005280000260000-19929.59FNMA MTG PYMNTS
703001070400020000-3693.16PORTFOLIO 15YR PRINC PYMNTS

<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> </tbody>

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
 
Upvote 0
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:

ABCDEFG
8000052

<tbody>
</tbody>
80000260000

<tbody>
</tbody>
-19929.6

<tbody>
</tbody>
FNMA MTG PYMNTS

<tbody>
</tbody>
FNMA P&I PAYMENTS

<tbody>
</tbody>
0

<tbody>
</tbody>
-19929.6

<tbody>
</tbody>
70302410

<tbody>
</tbody>
70400020000

<tbody>
</tbody>
-3693.16

<tbody>
</tbody>
PORTFOLIO 15YR PRINC PYMNTS

<tbody>
</tbody>
PAYMENTS CLEARING ACCOUNT

<tbody>
</tbody>
0

<tbody>
</tbody>
-3693.16

<tbody>
</tbody>

<tbody>
</tbody>





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

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

to

Code:
ary1(i, 3) =ary1(i, 6) + ary1(i, 7)
 
Last edited:
Upvote 0
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.
 
Upvote 0
***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

Code:
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
Code:
Worksheets(2)

and then your worksheet that is named "2"
Code:
Worksheets("2")
and that will get confusing down the road
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top