Excel Macro importing data to Excel Sheet

Question

I am tired looking solutions.I am using Excel Macro for importing data into excel sheet. Code itself gets data from another excel sheet and loop through the number of excel sheets given. For other sheets it is working fine but for some case it is importing data more then the required and randomly into any columns.Suppose it has to loop through the code 9 times ,then it is importing 9 correct rows(with correct excel cell format) and some additional random rows with random excel cell format My macro code is given below :

"shtData" is the Excel files from which the data has to be imported. "shtCurrent" is the sheet in which the macro is written and data is to be imported.

Set ACell = shtData.Cells.Find("Fund", LookIn:=xlValues, LookAt:=xlWhole, after:=shtData.Range("A1")).Offset(2, 0)
                Set BCell = ACell.End(xlDown)

    For Each DataCell In shtData.Range(ACell, BCell)


            Set rCell = shtCurrent.Range("A65000").End(xlUp).Offset(1, 0)

            rCell.Value = DataCell.Offset(0, 7).Value 'date
            rCell.Offset(0, 1).Value = DataCell.Offset(0, 8).Value 'date format
            rCell.Offset(0, 2).Value = "TEXT"
            rCell.Offset(0, 3).Value = "TEXT"
            rCell.Offset(0, 4).Value = "TEXT"
            ' null value required for rCell.Offset(0, 5)
            rCell.Offset(0, 6).Value = "100.00"
            rCell.Offset(0, 7).Value = "100.00"
            rCell.Offset(0, 8).Value = "100"
            rCell.Offset(0, 9).Value = "100"
            ' null value required for rCell.Offset(0, 10)
            rCell.Offset(0, 11).Value = RunDate 'date format
            rCell.Offset(0, 12).Value = "TEXT"
            rCell.Offset(0, 14).Value = shtData.Range("C5").Value 'Date format

    Next DataCell

Please , I am fed up with this. Any help will be appreciated.

This DomeCell.End(xlSomeDirection) function can do surprising things. To discover what is going on, add statements like "debug.print ACell.parant.name, ACell.address, BCell.address" and inspect the imediate window — Dirk Horsten

Rusan Kax Rusan Kax · Accepted Answer · 2015-01-09T14:11:35

Without more information, the code seems to work fine for me, once the worksheet objects were defined. Because your code was not a reproducible example - i.e. you omitted some details - I cannot tell how shtData was defined (if at all).

Please do try to post reproducible examples - as this helps in gaining more answers.

Anyway..I created two worksheets called shtData and shtCurrent. I reference them in the code using

Dim shtData, shtCurrent As Worksheet
Set shtData = Worksheets("shtData")
Set shtCurrent = Worksheets("shtCurrent")

I put the code below in a separate module, and use Option Explicit, as this forces the user/programmer to think about, and define, the variables he/she is using, and their types, etc, etc.

Here is your code, as a reproducible example (obviously need some values in shtData).

Option Explicit

Sub testtest()

Dim ACell, BCell, DataCell, rCell As Range
Dim shtData, shtCurrent As Worksheet

Dim RunDate As Date
RunDate = Now()

Set shtData = Worksheets("shtData")
Set shtCurrent = Worksheets("shtCurrent")

Set ACell = shtData.Cells.Find("Fund", LookIn:=xlValues, LookAt:=xlWhole, after:=shtData.Range("A1")).Offset(2, 0)
Set BCell = ACell.End(xlDown)


    For Each DataCell In shtData.Range(ACell, BCell)


            Set rCell = shtCurrent.Range("A65000").End(xlUp).Offset(1, 0)

            rCell.Value = DataCell.Offset(0, 7).Value 'date
            rCell.Offset(0, 1).Value = DataCell.Offset(0, 8).Value 'date format
            rCell.Offset(0, 2).Value = "TEXT"
            rCell.Offset(0, 3).Value = "TEXT"
            rCell.Offset(0, 4).Value = "TEXT"
            ' null value required for rCell.Offset(0, 5)
            rCell.Offset(0, 6).Value = "100.00"
            rCell.Offset(0, 7).Value = "100.00"
            rCell.Offset(0, 8).Value = "100"
            rCell.Offset(0, 9).Value = "100"
            ' null value required for rCell.Offset(0, 10)
            rCell.Offset(0, 11).Value = RunDate 'date format
            rCell.Offset(0, 12).Value = "TEXT"
            rCell.Offset(0, 14).Value = shtData.Range("C5").Value 'Date format

    Next DataCell
End Sub

Excel Macro importing data to Excel Sheet

1 Answers