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.

 

[shg]

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.

 

[Eric W]

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.

 

[DataBlake]

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

 

[Trying2learnVBA]

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.

 

[DataBlake]

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?

	A	B	C
Mystery Column	Refernce Data	Description
???
???
???
!!!

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]800162[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]8000052[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]800032[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]8000075[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]800162[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]8000052[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]800032[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]8000075[/TD]

</tbody>

Sheet2​

 

[Trying2learnVBA]

would you say it looks like this?

	A	B	C
Mystery Column	Refernce Data	Description
???
???
???
!!!

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: center"]2
[/TD]

[TD="align: right"]800162
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3
[/TD]

[TD="align: right"]8000052
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4
[/TD]

[TD="align: right"]800032
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5
[/TD]

[TD="align: right"]8000075
[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

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

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"]800162
[/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"]8000052
[/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"]800032
[/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"]8000075
[/TD]

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

 

[DataBlake]

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

	A	B	C	D	E
Reference	Reference to be changed	Debits	Credits	Description

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]800162[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]8000052[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]800032[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]8000075[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]

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

 

[DataBlake]

An example of non VBA you can do to get your expected results is if your sheets look like

	A	B	C	D	E
Reference	Reference to be changed	Debits	Credits	Description

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]800162[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]8000052[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]800032[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]8000075[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1

	A	B	C
Reference	Replacement	Description
80000620000-2	A giant can of beans
80000260000-1	A 16" can opener
80000230000-2	A phone without buttons
80000320000-1	A coffee mug but it has no bottom

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]800162[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]8000052[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]800032[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]8000075[/TD]

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

	A	B	C	D	E
Reference	Reference to be changed	Debits	Credits	Description
80000620000-2	A giant can of beans
80000260000-1	A 16" can opener
80000230000-2	A phone without buttons
80000320000-1	A coffee mug but it has no bottom

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]800162[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]500[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]8000052[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]500[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]800032[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]500[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]8000075[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]500[/TD]

</tbody>

Sheet1



[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas
[TABLE="width: 100%"]
<tbody>[TR]
[TH="align: left"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="align: left"]B2[/TH]
[TD]=VLOOKUP(A2,'2'!A:B,2,FALSE)[/TD]
[/TR]
[TR]
[TH="align: left"]B3[/TH]
[TD]=VLOOKUP(A3,'2'!A:B,2,FALSE)[/TD]
[/TR]
[TR]
[TH="align: left"]B4[/TH]
[TD]=VLOOKUP(A4,'2'!A:B,2,FALSE)[/TD]
[/TR]
[TR]
[TH="align: left"]B5[/TH]
[TD]=VLOOKUP(A5,'2'!A:B,2,FALSE)[/TD]
[/TR]
[TR]
[TH="align: left"]E2[/TH]
[TD]=VLOOKUP(A2,'2'!A:C,3,FALSE)[/TD]
[/TR]
[TR]
[TH="align: left"]E3[/TH]
[TD]=VLOOKUP(A3,'2'!A:C,3,FALSE)[/TD]
[/TR]
[TR]
[TH="align: left"]E4[/TH]
[TD]=VLOOKUP(A4,'2'!A:C,3,FALSE)[/TD]
[/TR]
[TR]
[TH="align: left"]E5[/TH]
[TD]=VLOOKUP(A5,'2'!A:C,3,FALSE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Trying2learnVBA]

Sheet1 comes in this way:
[TABLE="width: 654"]
<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>[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"]
<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>[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"]
<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>[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]

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