VBA Range Function "N/a" error 2042...

[tensionalvolt]

Hi guys,

First time posting to a thread like this so apologies if I ask this query in a convoluted manner.

That said, I am currently writing a macro for appending the range of an active worksheet to a .txt file
for further analysis. Whilst I have successfully written this for a test excel file, the worksheet I wish to have
this macro operate on contains NA() values within cells of the range I want to append. Subsequently, I receive
a 2042 error. I have tried implementing a way around this through use of the ISNA function but to no avail.

Here is a copy of the macro as it currently stands...

Sub AutoCollect()
'
' AutoCollect1 Macro
' Ideally appends data to a larger file for more stochastically valid data acquisition.
'




    Dim filename As String, lineText As String
    Dim myrng As Range, i, j
    
    filename = ThisWorkbook.Path & "\AC" & ".txt"
    
    Open filename For Append As #1 
    
    ' Make sure to set range appropriately for your data acquisition
    Set myrng = Range("Z794:AZ1426")
    
    For i = 1 To myrng.Rows.Count
        For j = 1 To myrng.Columns.Count
            lineText = IIf(j = 1, "", lineText & " ") & myrng.IF(Application.WorksheetFunction.NOT(Application.WorksheetFunction.IsNA(Cells(i, j))), "N/a", Cells(i,j))
            
        Next j
        Print #1 , lineText
    Next i
    
    Close #1 
    
    Application.Calculate
    
    MsgBox ("Done")
End Sub

Feel free to change the range and manually enter an NA() function for testing purposes. I will post my progress to this forum for future benefits. Hopefully we can resolve this together

All the best,


Tension

 

[RoryA]

Welcome to the forum.

Try this loop instead:

    For i = 1 To myrng.Rows.Count
        linetext = ""
        For j = 1 To myrng.Columns.Count
            If IsError(Cells(i, j).Value) Then
                linetext = linetext & " N/a"
            Else
                linetext = linetext & " " & Cells(i, j).Value
            End If
        Next j
        Print #1 , Mid$(linetext, 2)
    Next i

 

[tensionalvolt]

Thanks mate!

This appears to have done the trick, I must leave for a meeting but I'll certainly be in touch
if I have any more queries! Much appreciated.


Warm regards,

Evan

 

[tensionalvolt]

Ok, I wish to thank you again for your help in resolving my issue with "Error 2042". However, I now have the issue of running this code where whilst I write the range I specify to a .txt file, that range starts from (1,1) as opposed to Z794 (26,794). For some reason it will no longer write the range I specify when changing "For i=1" to "For i=794". Here is a copy of the macro for convenience...

Sub AutoCollect()
'
' Autocollect Macro
' Autocollates data from multiple runs into an appended .txt file.




'


' Ideally appends data to a larger file for more stochastically valid data acquisition.
'




    Dim filename As String, lineText As String
    Dim myrng As Range, i, j
    
    filename = ThisWorkbook.Path & "\AC" & ".txt"
    
    Open filename For Append As #1 
    
    ' Make sure to set range appropriately for your data acquisition
    Set myrng = Range("$Z$794:$AZ$1426")
    
    For i = 794 To myrng.Rows.Count
        lineText = ""
        For j = 26 To myrng.Columns.Count
            If IsError(Cells(i, j).Value) Then
                lineText = lineText & " N/a"
            Else
                lineText = lineText & " " & Cells(i, j).Value
            End If
        Next j
        Print #1 , lineText
    Next i
    
    Close #1 
    
    Application.Calculate
    
    MsgBox ("Done")




' Keyboard Shortcut: Ctrl+e
'
End Sub

Thank you very much for you time and apologies if (when) this turns out to be a question with an obvious solution.



Warm regards,



Tension

 

[RoryA]

You need the loop to start at 1 but refer to Myrng.cells(I, j) rather than just cells(I, j)

 

[tensionalvolt]

I tried that initially however upon further inspection of the data collected, I realised that this macro would obtain the range of values (say B2:C3 being 4) yet they would always begin looping from A1 (1,1). This was why I thought I needed to change the loop start?

 

[RoryA]

Your code needs to loop through each row and each column in the range, therefore your counters (i and j) need to loop from 1 to myrng.Rows.Count and 1 to myrng.Columns.Count respectively. In range Z794:AZ1426 you have 633 rows and 27 columns. If you start the row loop at 794, you are looping from 794 to 633. so the code will not run at all.

What you need to do is refer to the cells of the range by index, not all the cells on the worksheet, so you need something like this (note the code tags as well!):

Sub AutoCollect()
'
' Autocollect Macro
' Autocollates data from multiple runs into an appended .txt file.




'


' Ideally appends data to a larger file for more stochastically valid data acquisition.
'




    Dim filename As String, lineText As String
    Dim myrng As Range, i, j
    
    filename = ThisWorkbook.Path & "\AC" & ".txt"
    
    Open filename For Append As #1
    
    ' Make sure to set range appropriately for your data acquisition
    Set myrng = Range("$Z$794:$AZ$1426")
    
    For i = 1 To myrng.Rows.Count
        lineText = ""
        For j = 1 To myrng.Columns.Count
            If IsError(myrng.Cells(i, j).Value) Then
                lineText = lineText & " N/a"
            Else
                lineText = lineText & " " & myrng.Cells(i, j).Value
            End If
        Next j
        Print #1  , lineText
    Next i
    
    Close #1 
    
    Application.Calculate
    
    MsgBox ("Done")


' Keyboard Shortcut: Ctrl+e
'
End Sub

Also, be aware that this will only work for a contiguous range.

 

[tensionalvolt]

This does make sense to me, I assumed initially that the dimensions of the range were evaluated from the Range() function. This then loops from 1 to the dimensions specified writing accordingly. However, I am still confused by referring to the range of the cells by index, rather than all the cells in the worksheet. This is where I think the problem lies as currently the macro collects 633 rows and 27 columns but not from the range specified, rather as if the range were Z794:A1 (e.g. the cell A841 is written into file multiple times whilst not in the specified range). Could a bug arise due to reformatted cells?

 

[RoryA]

I can't replicate what you are describing - the revised macro outputs only the range in question.

It does, however, append to the file so if you already had irrelevant data in an existing file by that name, you would still see it.

 

[tensionalvolt]

hmmm, I will try create a test file of similar size to attach and aid problem description. The "append" functionality is a good point however I usually delete the contents of the file before re-running the macro. Bear with me...