Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Can I Code a macro that has Saved Text descriptions based on a criteria?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  2. #2
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,612
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

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

    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.

  3. #3
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,647
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

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

    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.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  4. #4
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

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

    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-...tachments.html
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  6. #6
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Trying2learnVBA View Post
    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
    1 Mystery Column Refernce Data Description
    2 ??? 800162
    3 ??? 8000052
    4 ??? 800032
    5 !!! 8000075
    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
    Sheet2
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  7. #7
    New Member
    Join Date
    Aug 2019
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by BlakeSkate View Post
    would you say it looks like this?


    A B C
    1 Mystery Column Refernce Data Description
    2 ??? 800162
    3 ??? 8000052
    4 ??? 800032
    5 !!! 8000075
    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
    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.

  8. #8
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Trying2learnVBA View Post
    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-...ml#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
    1 Reference Reference to be changed Debits Credits Description
    2 800162 1000 500
    3 8000052 1000 500
    4 800032 1000 500
    5 8000075 1000 500
    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 by BlakeSkate; Aug 22nd, 2019 at 02:53 PM.
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  9. #9
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

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

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

    A B C D E
    1 Reference Reference to be changed Debits Credits Description
    2 800162 1000 500
    3 8000052 1000 500
    4 800032 1000 500
    5 8000075 1000 500
    Sheet1





    A B C
    1 Reference 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
    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
    1 Reference Reference to be changed Debits Credits Description
    2 800162 80000620000-2 1000 500 A giant can of beans
    3 8000052 80000260000-1 1000 500 A 16" can opener
    4 800032 80000230000-2 1000 500 A phone without buttons
    5 8000075 80000320000-1 1000 500 A coffee mug but it has no bottom
    Sheet1



    Worksheet Formulas
    Cell Formula
    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)
    Last edited by BlakeSkate; Aug 22nd, 2019 at 03:03 PM.
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  10. #10
    New Member
    Join Date
    Aug 2019
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Sheet1 comes in this way:
    Bank 8000052 221475896 43903 FNMA P&I PAYMENTS 0 0
    Bank 8000052 221475896 43903 FNMA P&I PAYMENTS 0 -19929.59
    Bank 131029 221475896 800-025 PAYMENTS CLEARING ACCOUNT 0 -633.16

    Sheet2 (I will create with all the cross references)
    8000052 80000260000-1 FNMA MTG PYMNTS
    131029 6100010000-5 FNMA MTG SVC INCOME



    This is what It want the end result to be. S1 should look like this
    Account Number GL Amount Description
    8000052 80000260000-1 -19929.59 FNMA MTG PYMNTS
    131029 6100010000-5 -633.16 FNMA MTG SVC INCOME

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •