0

I am in learning phase in vba. I am trying to store a value in a variable but not able do with Cell and also Range and it throws an 1004 error

Below is my code

Sub myself()

Dim str As String
Dim rock As String
Dim MaxStrLen As Integer
Dim StrLen As String
Dim LastRow As Long
Dim LastCol As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim i As Integer
Dim j As Integer

Dim FilePath As String

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

LastRow = Cells(Rows.Count, 1).End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

Open "C:\Users\Antony\Desktop\test.txt" For Output As #2

ws1.Activate

With ws1
For i = 0 To LastRow
For j = 0 To LastCol

.Range(.Cells(i, j)).Value = str

Next j

Next i

Print #2, str
End With

Close #2

End Sub

Highlighted line is the 1004 error. Please help to solve and store in a variable in Notepad.

Thanks in advance!

Improve this question
6
  • Range expects two cells, a start and a finish. Just use .Cells(i, j).Value = str Commented Mar 21, 2018 at 20:35
  • I think he wants the value of the cell stored in the variable str, so need to flip the sides of that assignment: str = .Cells(i, j).Value Commented Mar 21, 2018 at 20:35
  • And to output each cell value individually in your loop, move the Print line within your inner For j loop, or else you'll only ever print out the last value. Commented Mar 21, 2018 at 20:36
  • str = .Cells(i, j).Value tried this line too still throws an error 1004 Commented Mar 21, 2018 at 20:41
  • 1
    @AntonyPPeter The problem is that your For loops start at 0. There is no row 0 and there is no column 0, that's what's causing your error. Change your loops to start at 1. Commented Mar 21, 2018 at 20:43

1 Answer 1

Reset to default
2

just use

.Cells(i, j).Value = str

BTW you'd better explicitly qualify your range references up to the worksheet object (and, if multiple workbooks are involved, up to the workbook object) , otherwise it would implicitly assumed ActiveSheet (and ActiveWorkbook)

so, instead of

LastRow= Cells(Rows.Count, 1).End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

use

LastRow= ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
LastCol = ws1.Cells(1, ws1.Columns.Count).End(xlToLeft).Column

or 

With ws1
    LastRow= .Cells(.Rows.Count, 1).End(xlUp).Row
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

so your code could be refactored as follows:

Option Explicit

Sub myself()

    Dim str As String
    Dim LastRow As Long
    Dim LastCol As Long
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim i As Long
    Dim j As Long

    Dim FilePath As String

    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")


    Open "C:\Users\Antony\Desktop\test.txt" For Output As #2

    With ws1
        LastRow = .Cells(.Rows.count, 1).End(xlUp).Row
        LastCol = .Cells(1, .Columns.count).End(xlToLeft).Column

        For i = 1 To LastRow
            For j = 1 To LastCol
                str = .Range(.Cells(i, j)).Value
                Print #2, str
            Next
        Next
    End With

    Close #2

End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

Initially I tried the same but it shows the same error.
@DisplayName the problem isn't how he's defining the end, it's how he's defining the beginning. From his original post: For i = 0 To LastRow Because there is no row 0, it will always result in the 1004 error.
but it's the right syntax, so what's the outcome now?
You are correct, syntax is fine. OP just need to make his For loop start at an actual row number instead of 0
I used both options still it fails to execute
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.