excel import code

[dekutoski]

I need an import code to copy the values of an above row in Excel when the row is empty. Please somebody help! There is no primary key to update after the import.

 

[DPlank]

May I be the first to say Eh?

Can you explain what you need to do a bit more clearly please.

Cheers,
Dave

"Yes, I'll stop finding bugs in the software - as soon as you stop writing bugs into the software." <-- Me

For all your testing needs: Forum1393

 

[SkipVought]

Hi,

Your question in not very clear.

You can IMPORT a module into the VB Editor. There is no key for this. However, your VB Project may be protected, if that is what you are referring to.

File > Import file

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[dekutoski]

not a module. I need to import an excel file where there are gaps in the data. the names do not reappear in corresponding rows previuosly. I need to copy the data down. This is what Control ' does. I need to apply it to the entire worksheet.

 

[SkipVought]

"I need to import an excel file where there are gaps in the data."

You have not provided the necessary basic information about your worksheet with missing data. You cannot insert a FILE where there is missing data.

Is there any key data in the row containing missing data for a lookup?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[dekutoski]

LOCATION EE_ID EE_NAME xDATE JOB RATE TIME_IN TIME_OUT BREAK_START BREAK_END UNPAID_TIME PAID_TIME CLOCK_TIME
Ontario 843196 ABEYTA, GABRIEL 11/26/07 1000 $7.50 9:30a 1:44p 10:40a 10:53a 0:00 4:14 4:14
Ontario 843196 ABEYTA, GABRIEL 11/27/07 1000 $7.50 11:30a 2:00p 0:00 2:30 2:30
Ontario 843196 ABEYTA, GABRIEL 12/01/07 1000 $7.50 11:30a 11:35a 0:00 0:05 0:05
Ontario 843196 ABEYTA, GABRIEL 12/02/07 1000 $7.50 10:12a 5:58p 12:30p 1:00p 0:30 7:16 7:46
5:58p 4:54p 5:05p
Ontario 873564 ABEYTA, IRENE 11/26/07 2500 $10.00 9:00a 2:31p 12:58p 1:17p 0:30 5:01 5:31
2:01p 2:31p
Ontario 873564 ABEYTA, IRENE 11/27/07 2500 $10.00 1:00p 8:04p 3:37p 4:07p 0:30 6:34 7:04

 

[dekutoski]

I need to fill the blanks before the import

 

[SkipVought]

You need to help yourself out here. You are NOT providing the necessary information for anyone to provide a solution. This is like pulling teeth, trying to get you to be CLEAR, CONCISE & COMPLETE.

So there's missing data in BREAK_START

How do you propose to find the data to update those rows?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[DPlank]

So basically what you want to achieve is to have no blank cells in the data; where there is a blank cell, you want to insert a copy of the data in the nonblank cell above it?

Is this what you're after?

Cheers,
Dave

"Yes, I'll stop finding bugs in the software - as soon as you stop writing bugs into the software." <-- Me

For all your testing needs: Forum1393

 

[dekutoski]

Exactly what I need. I have never had to use code for this because of a program called Monarch that I've used at my old job. It does that for you when extracting the data. I really hope you can help.

 

[SkipVought]

Oh, OK. The row above.

But what happens if the FIRST row of data is empty?

dim c as range, r as range
for each c in range([A1], [A1].end(xltoright))
  for each r in range([A2], [A2].end(xldown))
     if r.value = "" then r.value = r.offset(-1).value
  next
next

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[dekutoski]

shouldn't ever be blank, it has field names.

..going to try it

hope it works!

 

[dekutoski]

it errors out on

compile error: user type not defined

and it highlights "c As range

 

[dekutoski]

is this code supposed to go in excel?

 

[SkipVought]

This works. I tested it.

    Dim c As Range, r As Range
    With ActiveSheet
        For Each c In .Range(.[A1], .[A1].End(xlToRight))
          For Each r In .Range(.[A2], .[A2].End(xlDown))
             If Intersect(r.EntireRow, c.EntireColumn).Value = "" Then _
                Intersect(r.EntireRow, c.EntireColumn).Value = _
                    Intersect(r.EntireRow, c.EntireColumn).Offset(-1).Value
          Next
        Next
    End With

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[dekutoski]

I can't get this to work in excel. it doens't error out though.

 

[dekutoski]

it is skipping the line

Intersect(r.EntireRow, c.EntireColumn).Value = _

I tried

value<>"" and that didn't work

PLEASE HELP

 

[dekutoski]

it's not determining that the cells are empty and is bypassing the rest of the code.

 

[SkipVought]

Did you copy & paste the code that I posted.

You do not have to tyep ANYTHING except Sub...End Sub bracketing.

It works!

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[dekutoski]

Intersect(r.EntireRow, c.EntireColumn).Value = _
Intersect(r.EntireRow, c.EntireColumn).Offset(-1).Value

it skips both of these lines