help needed with format command using .Offset method

[perry_fawn]

http://www.mrexcel.com/forum/showthr...=1#post2564472

Hello,

I have been working with a code and I cannot seem to get the format function to work when I use the .Offset method as a cell reference. Can anyone help me with this problem?

Here is my code:

Public Sub AdjustedExtract()
        Const DELIMITER As String = "" 'Normally none
        Const PAD As String = " "   'or other character
        Const Header As String = "Copy header here"
        Const Footer As String = "Copy footer here"
        Dim vFieldArray As Variant
        Dim vFormatArray As Variant
        Dim myRecord As Range
        Dim nFileNum As Long
        Dim i As Long
        Dim sOut As String
        Dim sMyString As String
        
        
        'formatting should be done, then field lengths, then remove periods
        vFieldArray = Array(1, 6, 10, 2, 1, 1, 8, 8, 9, 9, 9, 2, 9, 2, 9, 1, 2, 2, 9, 8, 11, 9, 9, 9, 9, 3)
        vFormatArray = Array("General", "General", "General", "General", "General", "yyyymmdd", "yyyymmdd", "00000.0000", "00000.0000", "000000.000", "General", "0000000.00", "00", "0000000.00", "General", "General", "General", "0000000.00", "yyyymmdd", "000000000.00", "0000000.00", "0000000.00", "0000000.00", "000")
        nFileNum = FreeFile
        Open "Test.txt" For Output As #nFileNum
        Print #nFileNum, Header
        For Each myRecord In Range("A2:A" & _
                Range("A" & Rows.Count).End(xlUp).Row)
            With myRecord
                For i = 0 To UBound(vFieldArray)
                    sMyString = Format(.Offset(0, i).Value, vFormatArray(i))
                    sMyString = WorksheetFunction.Substitute(sMyString, ".", "")
                    sOut = sOut & DELIMITER & Left(sMyString & _
                            String(vFieldArray(i), PAD), vFieldArray(i))
                Next i
                Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
                sOut = Empty
            End With
        Next myRecord
        Print #nFileNum, Footer
        Close #nFileNum
        MsgBox sString
    End Sub

Thanks,
Fawn