Runtime error with ActiveCell.offset

[Eowyne]

Hi there, Im running the following code:

Function Fill_Fields(xlWS, objXL, MyRec)
FieldCount = 0
    MyRec.MoveFirst
    xlWS.Range("A2").select
    While Not MyRec.EOF
        For Each fld In MyRec.Fields
            With objXL.ActiveCell
                .NumberFormat = "@"
                .Value = UCase(fld.Value)
                .offset(0, 1).select
                FieldCount = FieldCount + 1
            End With
        Next fld
        MyRec.MoveNext
        objXL.ActiveCell.offset(1, -FieldCount).select
    Wend
    With xlWS.UsedRange
        .Font.Name = FontFace
        .Columns.AutoFit
        .HorizontalAlignment = 1
        .Borders.LineStyle = 1
    End With
    xlWS.Range("A:A").Columns.Hidden = True
    MyRec.Close
    Set MyRec = Nothing
End Function

and I get an error "1004" when my function adresses the ActiveCell.Offset(1, -FieldCount).

However, I need the -FieldCount (where it obviously crashes upon, since the function works great while useing -6) because the function is for multiple tables and they don't all have the same number of fields.

I'm Using the offset to reset the pointer to the rule below the first, and then -FieldCount to the left (so it goes from A11 to B1)..

any ideas on how to fix this?

[ajetrumpet]

what variable type if FieldCount?? does it match the arg types?? I believe they are LONGS for that method.

have you tried "-(FieldCount)" instead?? I don't know if that would work...

have you tried resetting the FieldCount first, so you can eliminate the (-) sign?? e.g.-

fieldcount = fieldcount - (fieldcount * 2)
activecell.offset(1, fieldcount).select

and are you sure your OFFSET arguments are in the right order?? rows are first and columns are second, right?? if columns are first, that's your error.

[Eowyne]

what variable type if FieldCount?? does it match the arg types?? I believe they are LONGS for that method.

have you tried "-(FieldCount)" instead?? I don't know if that would work...

have you tried resetting the FieldCount first, so you can eliminate the (-) sign?? e.g.-

fieldcount = fieldcount - (fieldcount * 2)
activecell.offset(1, fieldcount).select

and are you sure your OFFSET arguments are in the right order?? rows are first and columns are second, right?? if columns are first, that's your error.

it's indeed rows first, columns second.

You were right about the variable type of FieldCount,
It has to be 'Long' for it to work.

Thanks about that!