Solved

excel autofill with various ranges

Posted on 2013-05-13
12
277 Views
Last Modified: 2013-05-20
Hello,
I have a question on how to possibly make this excel spreadsheet work in access DB.
I have to autofill the names of the companies with the times entries before importing the spreadsheet into access for further
The issue is that the range for autofill will change because the spreadsheet is exported monthly from QuickBooks.
Attached is the sample of the spreadsheet. Any help is appreciated.
QBTime-sample.xlsx
0
Comment
Question by:Harter
  • 6
  • 6
12 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 39164578
Do you need column C data autofilled down as well as column B data?
0
 

Author Comment

by:Harter
ID: 39164644
no, I was just going to delete B. I need to associate each row of data with date, name and duration to the data in column C, so that when I run the import macro into Access I can work with data. Essentially the access database will keep track of the duration and dates per client name. The user will run reports based on the date to see the total duration (basically time)
that person spent working with a particular client.
thanks
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39164677
This seems to do what you describe. I am assuming that you will eventually eliminate the Total rows (pre or post import).

Option Explicit

Public Sub Q_28126449()
    Dim rng As Range
    Dim rngEnd As Range
    Dim rngCol As Range
    Dim boolStop As Boolean
    For Each rngCol In ActiveSheet.Range("B:B").Columns
        Set rng = rngCol.Cells(1, 1)
        Set rng = rng.End(xlDown)
        boolStop = False
        Do
            Set rngEnd = rng.End(xlDown)
            If rngEnd.Row = ActiveSheet.Rows.Count Then
                Set rngEnd = ActiveSheet.Cells(rngEnd.Row, 11).End(xlUp).Offset(0, -(11 - rngCol.Column))
                boolStop = True
            Else
                Set rngEnd = rngEnd.Offset(-1)
            End If
            If rng.Row = rngEnd.Row Then
            Else
                ActiveSheet.Range(rng, rngEnd).FillDown
            End If
            Set rng = rngEnd.Offset(1)
        Loop Until boolStop
    Next
End Sub

Open in new window

0
 

Author Comment

by:Harter
ID: 39166046
I copied your code into VB in excel and ran it and it didnt do anything. Obviously i am not running it correctly. Should this code be put into AutoExec module into the spreadsheet, but the spreadsheet will be overwritten when the user exports the data.
Any suggestions are appreciated.
thanks
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39166056
In order to run this in a saved/opened workbook, you will need to save the workbook in xlsm format.
0
 

Author Comment

by:Harter
ID: 39166077
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 45

Expert Comment

by:aikimark
ID: 39166231
Change the range in the code to "C:C"

The notebook you posted with your question had this data in column B.  The code is acting on column B.
0
 

Author Comment

by:Harter
ID: 39166475
The code deleted all the data in Column C.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39166972
Ah.  I hadn't notice before that the numeric values have been moved from column K to column L.  Please try this version of the code.
Option Explicit

Public Sub Q_28126449()
    Dim rng As Range
    Dim rngEnd As Range
    Dim rngCol As Range
    Dim boolStop As Boolean
    For Each rngCol In ActiveSheet.Range("B:B").Columns
        Set rng = rngCol.Cells(1, 1)
        Set rng = rng.End(xlDown)
        boolStop = False
        Do
            Set rngEnd = rng.End(xlDown)
            If rngEnd.Row = ActiveSheet.Rows.Count Then
                Set rngEnd = ActiveSheet.Cells(rngEnd.Row, 12).End(xlUp).Offset(0, -(12 - rngCol.Column))
                boolStop = True
            Else
                Set rngEnd = rngEnd.Offset(-1)
            End If
            If rng.Row = rngEnd.Row Then
            Else
                ActiveSheet.Range(rng, rngEnd).FillDown
            End If
            Set rng = rngEnd.Offset(1)
        Loop Until boolStop
    Next
End Sub

Open in new window

0
 

Author Comment

by:Harter
ID: 39180925
sorry for the late response. The code works great, however, how do i delete the total row? I want to keep the total row where 'total' client is in column B but not in C. If that makes sense?
here is the pic.
thank you so much for your assistance.
keep-total-in-column-B.JPG
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 39181412
The simplest way is to put a filter on the columns and filter for the word "total".  Then delete those rows.  Since you are already manually editing the worksheet, this would be one additional step in the process.

You might also tweak the code that I posted to iterate through the cells in the column, looking for "total" and deleting the entire row.  If you do this, loop from the last row to the first row, since deletions can mess up the looping process.
0
 

Author Closing Comment

by:Harter
ID: 39181660
Awesome, thanks for your help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

My experience with Windows 10 over a one year period and suggestions for smooth operation
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now