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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
If you were using formulas, it would be a simple VLOOKUP, no?

So put a lookup table on a (perhaps hidden) sheet and use that in your code.
 
Upvote 0
Where do you have the descriptions saved? Is there a table in the spreadsheet somewhere? Frankly, this sounds like you could do it with a simple VLOOKUP without needing VBA at all. But if you do want VBA, you would read the descriptions into a table, possibly a dictionary, then read down column A, get the matching description from the table and enter it into column C. It would be a fairly short routine.
 
Upvote 0
if you want to do this without VBA its probably easier as Shg and Eric have said.
if you want to do this with VBA you're going to have to give us more information on where the information is stored.
being hyper specific (but concise) will help

as i suggested in your other post you can post snippets of your sheets using the following link:
https://www.mrexcel.com/forum/about-board/508133-attachments.html
 
Upvote 0
Guys,

I do not want you to think I want you to do my work.
So, this is my common sense amateur solution and it works. Also, my boss wants to see something by this week. So, I first got it done rooky style.
After I show him I will be able to learn advance tricks from you all.


Sub FICS_MA_Voucher()

'''''''''''''''
'FICS MA ENTRY'
'''''''''''''''


Worksheets("1").Select
Dim lRow As Long
Dim iCntr As Long
lRow = 20000
For iCntr = lRow To 1 Step -1
If Cells(iCntr, 6) = 0 And Trim(Cells(iCntr, 7)) = 0 Then
Rows(iCntr).Delete
End If
Next
Columns("C:E").Delete
'Range("B:B").Copy Destination:=Sheets("2").Range("B1")
'Range("a1").Find ("xxxxx")
'
'Worksheets("1").Select
'
'Columns("B").Replace "8000052", replacement:="80000260000-1", lookat:=xlWhole
'Range("B:B").Replace "800162", "80000620000-2"

Sheets("2").Range("B:B").Copy Destination:=Sheets("1").Range("A1")

Worksheets("2").Columns("B").Replace "8000052", replacement:="FNMA MTG PYMNTS", lookat:=xlWhole
'Worksheets("2").Columns("B").Replace "800162", "IFCU FHLB P&I DUE PYMNTS"

Sheets("2").Range("B:B").Copy Destination:=Sheets("1").Range("E1")
'Range("F2:F50").FormulaR1C1 = "=RC[-3]+RC[-2]"
'Range("F2:F50").Copy
'Range("C2").PasteSpecial Paste:=xlPasteValues
' Columns("D:D").Delete
' Columns("E:E").Delete
' Range("B1").FormulaR1C1 = "GL"
' Range("C1").FormulaR1C1 = "Amount"
' Range("D1").FormulaR1C1 = "Description"
' Columns("D:D").EntireColumn.AutoFit
'Sheets("1").Range("A1").Select

That is my solution. It works.

Now I would like to learn better methods.
I will add a sheet that has the reference data I get on the csv file to Col A
I will add what I want that reference data changed to in Col B.
In Col C I will type the text description.

Having a sheet with all that info - I'd love code that can look like the first part of my macro. The rest was all very manual code.

This is what I want as an end result.
In sheet1 I paste special the contents from the csv file I download.

I then want run a macro that cross references the data in Col B of sheet1 to my data in sheet2. S2 col a has the same data as s1. s2 col b has what I want s1 col b to change too plus take the description in s2 col c to s1 col c or any col i can arrange anyway once the code works.
 
Upvote 0
I will add a sheet that has the reference data I get on the csv file to Col A

would you say it looks like this?


ABC
1Mystery ColumnRefernce DataDescription
2???800162
3???8000052
4???800032
5!!!8000075

<tbody>
</tbody>
Sheet1

ABC
1Reference DataReplacementDescription
280016280000620000-2A giant can of beans
3800005280000260000-1A 16" can opener
480003280000230000-2A phone without buttons
5800007580000320000-1A coffee mug but it has no bottom

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2
 
Upvote 0
would you say it looks like this?


A
B
C
1
Mystery Column
Refernce Data
Description
2
???
800162
3
???
8000052
4
???
800032
5
!!!
8000075

<tbody>
</tbody>
Sheet1

A
B
C
1
Reference Data
Replacement
Description
2
800162
80000620000-2
A giant can of beans
3
8000052
80000260000-1
A 16" can opener
4
800032
80000230000-2
A phone without buttons
5
8000075
80000320000-1
A coffee mug but it has no bottom

<tbody>
</tbody>
Sheet2

Yes!
I will create S2 with A B an C data.

From S1 the only thing that matters is in Col B.
S1 varies in how many of those references come in but the References are the same -

S1 has 3 cols that matter - Col B and two other cols that have debits or credits.

I want S1 to end up looking like
Col A Reference Col B Replacement Col C Debits Col D Credits Col E Description.
 
Upvote 0
I want S1 to end up looking like
Col A Reference Col B Replacement Col C Debits Col D Credits Col E Description.

okay! now were getting somewhere. Can you paste a sample (you can change sensitive data) using the html maker addin of your sheets prior to running the code?
and then again after running the code?
just so i can see exactly what i'm looking to do.

html maker addin is found here:
https://www.mrexcel.com/forum/about-board/508133-attachments-post2507729.html#post2507729

after installing the addin you can highlight A1:E5 of your sheet and paste inside of a forum post like this

ABCDE
1ReferenceReference to be changedDebitsCreditsDescription
28001621000500
380000521000500
48000321000500
580000751000500

<tbody>
</tbody>
Sheet1

after you do this i can run you through how i would clean up your code and possibly improve it.
P.S this kind of table will help clarify anything in your future posts as well, so i highly recommend getting the hang of using HTML Maker Addin
 
Last edited:
Upvote 0
An example of non VBA you can do to get your expected results is if your sheets look like

ABCDE
1ReferenceReference to be changedDebitsCreditsDescription
28001621000500
380000521000500
48000321000500
580000751000500

<tbody>
</tbody>
Sheet1





ABC
1ReferenceReplacementDescription
280016280000620000-2A giant can of beans
3800005280000260000-1A 16" can opener
480003280000230000-2A phone without buttons
5800007580000320000-1A coffee mug but it has no bottom

<tbody>
</tbody>
Sheet2


you can just use vlookup to get the values from sheet 2 and just copy the formula down from B2 and E2
example:

ABCDE
1ReferenceReference to be changedDebitsCreditsDescription
280016280000620000-21000500A giant can of beans
3800005280000260000-11000500A 16" can opener
480003280000230000-21000500A phone without buttons
5800007580000320000-11000500A coffee mug but it has no bottom

<tbody>
</tbody>
Sheet1



Worksheet Formulas
CellFormula
B2=VLOOKUP(A2,'2'!A:B,2,FALSE)
B3=VLOOKUP(A3,'2'!A:B,2,FALSE)
B4=VLOOKUP(A4,'2'!A:B,2,FALSE)
B5=VLOOKUP(A5,'2'!A:B,2,FALSE)
E2=VLOOKUP(A2,'2'!A:C,3,FALSE)
E3=VLOOKUP(A3,'2'!A:C,3,FALSE)
E4=VLOOKUP(A4,'2'!A:C,3,FALSE)
E5=VLOOKUP(A5,'2'!A:C,3,FALSE)

<tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:
Upvote 0
Sheet1 comes in this way:
Bank800005222147589643903FNMA P&I PAYMENTS00
Bank800005222147589643903FNMA P&I PAYMENTS0-19929.59
Bank131029221475896800-025PAYMENTS CLEARING ACCOUNT0-633.16
<colgroup><col width="117" style="width: 88pt; mso-width-source: userset; mso-width-alt: 4278;"> <col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;"> <col width="146" style="width: 110pt; mso-width-source: userset; mso-width-alt: 5339;"> <col width="247" style="width: 185pt; mso-width-source: userset; mso-width-alt: 9033;"> <col width="70" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <tbody> </tbody>

Sheet2 (I will create with all the cross references)
800005280000260000-1FNMA MTG PYMNTS
1310296100010000-5FNMA MTG SVC INCOME
<colgroup><col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;" span="2"> <col width="160" style="width: 120pt; mso-width-source: userset; mso-width-alt: 5851;"> <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
<colgroup><col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;" span="2"> <col width="74" style="width: 56pt;"> <col width="306" style="width: 230pt; mso-width-source: userset; mso-width-alt: 11190;"> <tbody> </tbody>

Btw - I got this to work using lots of unnecessary code.
S1
1-I delete rows if col f & g = 0 so that I only get left with data I need to deal with.
2-Copy and paste col b to S2 Col B
3 - I do a find & replace on S1 (74 lines of code lol) Col(b) replace xxx replacement xxxx
4-Copy S2 Col b to S1 Col a
5-Find replace Col B on S2 to my desired description. Another 74 lines of code.
6-Copy paste S2 Col B to S1 Col E
7-On S1 Col F i do =f+g (to get debits and credits on the same col)
8-Copy paste special to col c

Then Simpe delete cols not needed
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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