Solved

excel autofill with various ranges

Posted on 2013-05-13
12
283 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

856 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