Run Time Error '1004': Method 'Range' of object '_Worksheet' failed

[flagen]

I am trying to take a column of data that I want to paste in five different columns. I want it to go:
1 to 12345
2 12345
3 12345
4 12345
5 12345
1
2
3
4
5



... and so on. Here is the code:

Option Explicit
Sub ReArrangeCells()


  Dim ws As Worksheet, LastRow As Long
  Set ws = Excel.ActiveSheet


  LastRow = Range("G71311").End(xlUp).Row


  Dim i As Long, j As Long, FromCell As Range, ToCell As Range, sNewCol As String, sNewRow As String
  For i = 1 To LastRow


    Set FromCell = ws.Range("G" & i)                       'the cell we want to move
    sNewCol = IIf(i Mod 5 = 0, Chr$(79), Chr$((i Mod 5) + 25))
    sNewRow = IIf(i Mod 5 = 0, (i \ 5), (i \ 5) + 1)
    Set ToCell = ws.Range(sNewCol & sNewRow)               'the cell we want to copy the data to


    FromCell.Copy ToCell
    If i <> 1 Then FromCell.Clear


    If i Mod 100 = 0 Then DoEvents


  Next i


End Sub

Here is the line the error occurs:

Set ToCell = ws.Range(sNewCol & sNewRow)               'the cell we want to copy the data to

I'm not sure how to adjust it.

 

[Trebor76]

Hi flagen,

I'd say the issue is that the sNewCol variable is not returning a letter and so the ToCell range cannot be set.

HTH

Robert

 

[Rick Rothstein]

Set FromCell = ws.Range("G" & i) 'the cell we want to move
sNewCol = IIf(i Mod 5 = 0, Chr$(79), Chr$((i Mod 5) + 25))
sNewRow = IIf(i Mod 5 = 0, (i \ 5), (i \ 5) + 1)
Set ToCell = ws.Range(sNewCol & sNewRow) 'the cell we want to copy the data to
[/code]

What character/column-letter do you expect the red highlighted text to generate?

Perhaps you meant the 25 to be 65?

 

[flagen]

Hi Rick and Trebor,

Ah, that part was edited from another forum post I found that was attempting to move data into 8 different columns. I have never done anything with Chr values so I wasn't sure how it worked until reading a list of them for a little while, it should have been:

sNewCol = IIf(i Mod 5 = 0, Chr$(79), Chr$((i Mod 5) + 74))

I want it posting to column O, then the 4 columns after. Thank you for the help and for recognizing my mistake!