Tag Archives: Excel

Computer Programming, Excel programming

We can all stand to learn something at times

So I’ve been in the computer world for years, sometimes I think it’s too many years!

And I learned something the other day. I know it’s one of those things that we all say at one time “Dang, it would be nice if I could do this…” Well, I learned something in Excel … you can sort on more than just the value in the cell.

SortOn

 

I could have used this so many times, if only I would have known it was there. So there’s egg on my face on this one!!!!

Access Database Programming

When does 2=1

When you have two sets of information that are calculated separately, but yet need to be combined into one result.

Confusing, maybe, but not once you get the meaning. You have an inventory account that a bunch of purchases are going to, only they are not inventory. They actually should be charged to a different account. So you need to create the list of debits/credits for the bad account and for the correct account.

If you have 2 queries that have the exact same structure and to minimize transactions you want to combine them into one.

  1. Create a new SQL query
  2. Here is the text you will enter
    Select * from qryOne
    UNION ALL select * from qryTwo;
  3. Save the query
  4. View it – you should see the data from both queries combined into one set of columns.

I used this today to enter edits into an accounting system we had some purchases that were entered into the incorrect Account numbers. So i had one query with the wrong Account and the debit column had the $ in it. Then I had a second column and this time it had the correct Account on it and the $ were in the credit column – ran this little union query and they were both in one list and I copied and pasted into the journal entry screen.

If you only have a few row of data to fix, it is probably easier to do this in excel. But if you have lots (I had over 500 lines) which translates into over 1000 lines (one for debits and one for credits) this is a nice little tool to run and combine it all into one.

Excel programming

Collect Area from multiple tabs in Excel Workbook

Every once in awhile I need to refresh my “geekiness” and do some programming.  This week I had a project to take an excel file with 200 tabs (each employee had a tab with their information on it) and combine it into 1 worksheet so that it could be imported into a database without retyping all the information.

So I “googled” to see if I could find something that would do it, I found a couple of close ones, many way off ones so I took the best of them all and made this one.

  1. In preparing to run this macro I went into the first employee’s sheet and made sure that all the information was in A1.I1  – I used the “=F45” to get the information from other places on the sheet to where I wanted it.
  2. I then copied that formula to all the tabs – tedious, but very effective.  So I know had all the information I wanted to pull over into my one worksheet in the same layout and place on each tab.
  3. Then comes the macro:
    1. Creates another tab named “Combined” – which is also Sheet1
    2. Go to each tab in the workbook (Sheets.Count)
    3. Highlights the area that I have the formulas collection the information on each tab (A1.I1)
    4. Copies it (Selection.Copy)
    5. Make Combined Sheet the active sheet (Sheets(1).Activate)
    6. Go to the bottom of Column A in the worksheet – skip up to the last row of data in column A (the best way to explain this is if you were in the worksheet in column A, GoTo (ctrl-G) A65536 (way down the column) press the End key and then the Up key and it will bring you to the bottom of the data you collected). Then paste the values of what you copied in #D above.
      Range(“A65536”).End(xlUp)(2).PasteSpecial xlPasteValues
Sub CollectIt()
 Dim J As Integer
 On Error Resume Next
 Sheets(1).Select
 Worksheets.Add
 Sheets(1).Name = "Combined"
 For J = 2 To Sheets.Count
   Sheets(J).Activate
   Range("A1.I1").Select
   Selection.Copy
   Sheets(1).Activate
   Range("A65536").End(xlUp)(2).PasteSpecial xlPasteValues
 Next
End Sub