Solved

excel autofill with various ranges

Posted on 2013-05-13
12
289 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Office 365 Advanced Training for Admins

Special Offer:  Buy 1 course, get 2nd free!  Buy the 'Managing Office 365 Identities & Requirements' course w/ Accelerated TestPrep, and automatically receive the 'Enabling Office 365 Services' course FREE!

 

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

734 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