parse Excel file

29 May 2009 11:46 AM

George

I have got .xls file where two columns (F and J) must be parsed.
Columns look like this:

F J
aaa bbb
(empty) ccc
(empty) ddd
(empty) (empty)
fff ggg
(empty) rrr
(empty) vvv
(empty) (empty)

So, I need to go through these columns and copy data to other sheet:
column J - "as is"
column F - all but empty cells; copy and paste the same data from
column F down untill we reach two empty cells in columns F and J;
after that we copy and paste the next not empty data from column F.

We have to parse 23000 rows.

Help me please, I'm new to VBA.

29 May 2009 12:49 PM

MikeD

Show quote Hide quote

"George" <use***@ccx-grads.org> wrote in message news:eda84e77-b664-4bc1-b2b5-bbcacf41d84c@3g2000yqk.googlegroups.com...
> I have got .xls file where two columns (F and J) must be parsed.
> Columns look like this:
>
> F J
> aaa bbb
> (empty) ccc
> (empty) ddd
> (empty) (empty)
> fff ggg
> (empty) rrr
> (empty) vvv
> (empty) (empty)
>
> So, I need to go through these columns and copy data to other sheet:
> column J - "as is"
> column F - all but empty cells; copy and paste the same data from
> column F down untill we reach two empty cells in columns F and J;
> after that we copy and paste the next not empty data from column F.
>
> We have to parse 23000 rows.
>
> Help me please, I'm new to VBA.

You'll have better luck in an Excel newsgroup. Try microsoft.public.excel.programming.

--
Mike

29 May 2009 6:58 PM

Jennifer

On May 29, 6:46 am, George <use***@ccx-grads.org> wrote:
Show quoteHide quote

> I have got .xls file where two columns (F and J) must be parsed.
> Columns look like this:
>
> F J
> aaa bbb
> (empty) ccc
> (empty) ddd
> (empty) (empty)
> fff ggg
> (empty) rrr
> (empty) vvv
> (empty) (empty)
>
> So, I need to go through these columns and copy data to other sheet:
> column J - "as is"
> column F - all but empty cells; copy and paste the same data from
> column F down untill we reach two empty cells in columns F and J;
> after that we copy and paste the next not empty data from column F.
>
> We have to parse 23000 rows.
>
> Help me please, I'm new to VBA.

Sub Macro1()
    Dim X
    Dim Y
    Y = 1
    For X = 1 To 23000
        If Sheet1.Cells(X, 6).Value <> "" Then
            Sheet2.Cells(Y, 6) = Sheet1.Cells(X, 6).Value
            Y = Y + 1
        End If
    Next X

   For X = 1 To 23000
        Sheet2.Cells(X, 10) = Sheet1.Cells(X, 10).Value
    Next X
End Sub

31 May 2009 6:29 AM

George

Hi Jennifer,
it stops at line
" If Sheet1.Cells(X, 6).Value <> "" Then"
with message
"Run-time error '424'
Object required"

How do I fix it?

On May 29, 9:58 pm, Jennifer <J.Evans.1***@gmail.com> wrote:
Show quoteHide quote

> On May 29, 6:46 am, George <use***@ccx-grads.org> wrote:
>
>
>
> > I have got .xls file where two columns (F and J) must be parsed.
> > Columns look like this:
>
> > F J
> > aaa bbb
> > (empty) ccc
> > (empty) ddd
> > (empty) (empty)
> > fff ggg
> > (empty) rrr
> > (empty) vvv
> > (empty) (empty)
>
> > So, I need to go through these columns and copy data to other sheet:
> > column J - "as is"
> > column F - all but empty cells; copy and paste the same data from
> > column F down untill we reach two empty cells in columns F and J;
> > after that we copy and paste the next not empty data from column F.
>
> > We have to parse 23000 rows.
>
> > Help me please, I'm new to VBA.
>
> Sub Macro1()
> Dim X
> Dim Y
> Y = 1
> For X = 1 To 23000
> If Sheet1.Cells(X, 6).Value <> "" Then
> Sheet2.Cells(Y, 6) = Sheet1.Cells(X, 6).Value
> Y = Y + 1
> End If
> Next X
>
> For X = 1 To 23000
> Sheet2.Cells(X, 10) = Sheet1.Cells(X, 10).Value
> Next X
> End Sub