locked
Using VBA to Automate Excel 2010 Worksheets RRS feed

  • Question

  • Hello All,

    Pretty new to VBA, and really trying to learn by doing some hands on stuff. I think my solution is something that is relatively easy, just wondering if someone could let me know if VBA would be my best option for my situation.

    So, every month, I receive invoices for my company. These invoices are in the following format:

    100055599999999991234567  John Doe   65.55
    100055699999999991234567  Jim Smith   70.52

    The long number string in front of the name is an allocation string. From this string, I have to run a MID formula several times to pull out the first 4 numbers, then 10 digits starting at the 8th number, then finally, the last 7 numbers of the string.

    Finally, I use Excel to change the employee name to represent the actual invoice. For example, if the name "John Doe" was located in cell B1, I would use:

    ="Oct - 2012 - Verizon Wireless Charges -"&" "&B1

    After I parse out this information, I copy it all the way down the column, and it effectively does what I need it to do. I am just wondering if you guys think I could create a VBA macro and just do all the work once. Then, every month I could just click buttons and have all the work done for me?

    Thanks for your input all!

    Thursday, November 1, 2012 3:23 PM

All replies

  • Okay,

    So I've used a couple of recorded macros to get the solution I was looking for! They are programmed to buttons that I click to run each macro in succession. However, I am having a problem with the last one, and was wondering if someone could help me with it.

    So I basically have a button that when you click on it, the user is first prompted for the Month. After entering and pressing ok, they are then prompted for the year. Then finally, they are prompted for the type of invoice.

    After the user types in these 3 pieces of info, I would like the script to use those pieces of information. So, if we again use my example of the user "John Doe" in column E2:

    If the user enters: October --> 2012 --> Verizon. The output would become:

    October - 2012 - Verizon - John Doe.

    Here is my code. I think I have concatenated incorrectly:

    Dim str_month
    str_month = InputBox("Enter the current month.")
    Dim str_year
    str_year = InputBox("Enter the current year.")
    Dim str_type
    str_type = InputBox("Which invoice is this?")
    '
        Columns("E:E").Select
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Selection.ColumnWidth = 42.29
        Range("E1").Select
        ActiveWindow.SmallScroll Down:=-24
        ActiveCell.FormulaR1C1 = =" str_month ""&"" str_year ""&"" str_type ""&"" ""&RC[1]"
        Range("E2").Select
        Columns("E:E").ColumnWidth = 46
        Selection.AutoFill Destination:=Range("E2:E19"), Type:=xlFillDefault
        Range("E2:E19").Select
        Range("E1").Select
        Selection.AutoFill Destination:=Range("E1:E16"), Type:=xlFillDefault
        Range("E1:E16").Select
    End Sub

    Any suggestions are greatly appreciated.
    Thursday, November 1, 2012 6:35 PM
  • Option Explicit
    
    Sub FillIt()
    
        Dim str_month As String
        Dim str_year As String
        Dim str_type As String
        
        str_month = InputBox("Enter the current month.")
        str_year = InputBox("Enter the current year.")
        str_type = InputBox("Which invoice is this?")
        
        
        Range("e:e").Insert Shift:=xlToRight, _
            CopyOrigin:=xlFormatFromLeftOrAbove
        Range("e1:e19").Formula = str_month _
            & " - " & str_year & " - " & str_type & " - " _
            & [=indirect("r[0]c2",0)]
    
    End Sub
    
    
    check if helps..

    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Friday, November 2, 2012 11:35 AM
    Answerer
  • Hey Learning,

    Thanks for the response. It looks like it's close to doing what I need it to do.

    I had to change this:

    Range("e1:e19").Formula

    to:

    Range("e:e").Formula

    So it did the whole column. However, I'm getting an output that isn't what is expected. It looks like this:

    Oct - 2012 - Verizon - 9008

    I need it to add the name of the employee to the end. After shifting the cells to the right at the beginning of the script, the employee names are now in the F column.

    The 9008 looks like it may be coming from the B1 or B2 column from my excel sheet.

    Thanks again

    Friday, November 2, 2012 5:26 PM
  • Okay,

    So, i figured out why the 9008 was printing.

    The code was [=INDIRECT("r[0] c2",0) which was returning the value from the first row, second column.

    However, the names that I need (after the macro shifts the columns to the right by one) are in the 6th column, so I changed the code accordingly.

    I'm only having two issues now:

    1.) Each row in column F has a different name. The current code prints the same name from r0c6 over and over.

    2.) The code is causing the text to insert in the whole column all the way to the bottom of the spreadsheet. I need it to stop when there is no more data at the end. For example, the current invoice I'm using only goes to row 300. This will vary from invoice to invoice, but I would like it to stop once it hits the end.

    Thanks

    Friday, November 2, 2012 6:23 PM
  • Sub SecondFillIt()
        Dim str_month As String
        Dim str_year As String
        Dim str_type As String
        
        Dim rng As Range
        
        str_month = InputBox("Enter the current month.")
        str_year = InputBox("Enter the current year.")
        str_type = InputBox("Which invoice is this?")
        
        
        'set a range to only used portion of b column
        Set rng = Range(Range("b1"), Range("b1").End(xlDown)).Offset(, 3)
        'Change the B1 to your required column
        'Chnage the offset(,3) to your required column
        'I used 3 beacause e column is 3 column far from column b
        
        
        Range("e:e").Insert Shift:=xlToRight, _
            CopyOrigin:=xlFormatFromLeftOrAbove
        rng.FormulaR1C1 = "=" & Chr(34) & str_month _
            & " - " & str_year & " - " & str_type & " - """ _
            & "&r[0]c2"
            
         'Change the c2 to appropriate column
        'I used c2 beacause b column is 2nd column in worksheet
            
        rng.Value = rng.Value
        'to change the formula to fixed value...
        'You may ignore it if not required
            
    End Sub
    See the comments.It will help you in adjusting to your needs...

    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Saturday, November 3, 2012 6:57 AM
    Answerer