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.

 

[SkipVought]

Please COPY the complete code from your module, Sub to End Sub, and post here.

Skip,

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

 

[dekutoski]

I run it as a macro right?

I've never coded in Excel.

 

[dekutoski]

I DID CHANGE THE REF TO A2 SINCE THE HEADERS ARE ON 1


Sub Test()



Dim c As Range, r As Range
With ActiveSheet
For Each c In .Range(.[A2], .[A2].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


End Sub


please, you're my only hope

 

[dekutoski]

it is staying stuck on A2

 

[SkipVought]

You did not have to change A1 to A2?

It still works.

When it stops, hit the Debug button and tell me what statement is highlighted.

Try using this code...

Sub Test()
    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))
            With Intersect(r.EntireRow, c.EntireColumn)
               If Trim(.Value) = "" Then .Value = .Offset(-1).Value
            End With
          Next
        Next
    End With
End Sub

Skip,

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

 

[dekutoski]

it doesn't error out. if i step through it seems like it works but there aren't any changes.

please be patient with me, i really need the help

 

[dekutoski]

it is staying stuck on the first line

does there need to be a loop?

 

[dekutoski]

it goes to this one If Trim(.Value) = "" Then .

...but skips this one
Value = .Offset(-1).Value

 

[SkipVought]

Well it will skip if there's something in the cell.

Skip,

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

 

[dekutoski]

when i mouse over

If Trim(.Value)<value.

it says .value=<object variable or With block variable not set>

 

[dekutoski]

it never gets to it

 

[SkipVought]

every Value must have a DOT reference...
[tt]
.Value
[/tt]


Skip,

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

 

[SkipVought]

Did you copy, paste & RUN my code without changes?

Skip,

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

 

[dekutoski]

yes, i sware

 

[dekutoski]

run it as a macro in excel right?

 

[SkipVought]

send your workbook to

ii36250 at bellhelicopter dot textron dot com

I'll take a look.

Skip,

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

 

[dekutoski]

i sent it

i took the code out before i sent it

any help is soooooooooooooooooooo
appreciated

 

[SkipVought]

Actually you can store your workbook on box.net

This code accounts for the missing items in column A, wheras the former code assumed that column A was fully populated. I ran it on the data in your worksheet...

Sub Test()
    Dim c As Range, r As Range
    With ActiveSheet.[A1].CurrentRegion
        For Each c In ActiveSheet.Range(ActiveSheet.[A1], ActiveSheet.[A1].End(xlToRight))
          For Each r In ActiveSheet.Range(ActiveSheet.Cells(2, "A"), ActiveSheet.Cells(.Rows.Count, "A"))
            With Intersect(r.EntireRow, c.EntireColumn)
               If Trim(.Value) = "" Then .Value = .Offset(-1).Value
            End With
          Next
        Next
    End With
End Sub

Skip,

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

 

[SkipVought]

I observe other problems with your sheet.

None of the Date or Time columns have REAL Date/Time values. faq68-5827

Your NUMERIC columns are TEXT.

Place a 1 in an unused cell.

COPY

Select ALL the data.

Edit > Paste Special -- MULTIPLY

Your DATE column should now have NUMBERS. Format as Date

Your Currency columns should just have NUMBERS. Format as Currency

Your TIME columns, however, asre sill TEXT. They will need to be CONVERTED to Time Values using the TIME function.

Post in a new thread if you need help fixing this problem.

Skip,

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

 

[dekutoski]

i'm going to save it as a text file in the end anyway

i'll fix the type issues in access where i actually know what i'm doing.

thanks again for your help, i hope i don't run into issues with what you were talking about.