**** Autofill!

L

LLCoolDL

New Member
Joined
May 4, 2011
Messages
16
Hi,

What is wrong with this autofill code?

Range(Cells(m, 5), Cells(l, 5)).Select
Selection.AutoFill Destination:=Range(Cells(m, 5), Cells(n, 5)), Type:=xlFillDefault

I get an error with the line selection.autofill

Any ideas?

By the way the error code is 1004. Which suggests it's trying to copy and paste a whole row/column or more than 2516 rows/columns. It shouldn't be as it's part of a loop copying 47 rows at a time.
 
Last edited:
Sort by date Sort by votes
This seems to work, had a bit of a problem with the formatting at first but I thing I fixed it.

<S>If it's still not working then uncomment the commented line.</S>

Just checked that and the formatting is needed so I uncommented it.
Code: 
Option Explicit
 
Sub TranEnergyTime()
Dim wsSrc As Worksheet
Dim wsDst As Worksheet
Dim rngDate As Range
Dim rngTimes As Range
Dim rngValues As Range
Dim rngDst As Range
Dim NoTimes As Long
 
    Set wsSrc = Worksheets("Sheet1")
 
    Set wsDst = Worksheets("Sheet2")
 
    Set rngDate = wsSrc.Range("A2")
 
    Set rngTimes = wsSrc.Range("C1", wsSrc.Cells(1, Columns.Count).End(xlToLeft))
 
    wsDst.Range("A1:C1") = Array("Date/Time", "Measured In", "Value")
 
    Set rngDst = wsDst.Range("A2")
 
    NoTimes = rngTimes.Columns.Count
 
    While rngDate.Value <> ""
 
        rngDst.Resize(NoTimes) = rngDate
 
        rngDst.Resize(NoTimes).Offset(, 1) = rngDate.Offset(, 1)
 
        Set rngValues = rngDate.Offset(, 2).Resize(, NoTimes)
 
        rngTimes.Copy
 
        rngDst.PasteSpecial xlValues, xlPasteSpecialOperationAdd, Transpose:=True
 
        rngDst.Resize(NoTimes).NumberFormat = "dd/mm/yyyy hh:mm"
 
        rngValues.Copy
 
        rngDst.Offset(, 2).PasteSpecial xlValues, Transpose:=True
 
        Set rngDate = rngDate.Offset(1)
 
        Set rngDst = rngDst.Offset(NoTimes)
 
    Wend
 
    rngDst.Resize(, 3).EntireColumn.AutoFit
 
End Sub

PS it adds the measured in unit(?) for each row - wasn't sure if that's what you wanted.
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
MrKowz said:
Try:

Code: 
Public Sub LLCoolDL()
Dim tempdte             As Date, _
    tempmeas            As String, _
    TimeAry(1 To 48)    As Date, _
    TempVal(1 To 48)    As Long, _
    i                   As Long, _
    j                   As Long, _
    LR                  As Long
 
LR = Range("A" & Rows.Count).End(xlUp).Row
For j = 3 To 50
    TimeAry(j - 2) = Cells(1, j)
Next j
For i = LR To 2 Step -1
    tempdte = Range("A" & i).Value
    tempmeas = Range("B" & i).Value
    For j = 3 To 50
        TempVal(j - 2) = Cells(i, j)
    Next j
    Rows(i).Delete
    Rows(i & ":" & i + 47).Insert Shift:=xlDown
    Range(Cells(i, 1), Cells(i + 47, 1)).Value = tempdte
    Range("B" & i).Value = tempmeas
    For j = 1 To 48
        Cells(i + j - 1, 3).Value = TimeAry(j)
        Cells(i + j - 1, 4).Value = TempVal(j)
    Next j
Next i
End Sub

It isn't the most effecient code, but it works. :biggrin:
Click to expand...

I'm getting an error message at CODE:

TimeAry(j - 2) = Cells(1, j)

The error is type mismatch???
 
Upvote 0
Norie said:
This seems to work, had a bit of a problem with the formatting at first but I thing I fixed it.

<S>If it's still not working then uncomment the commented line.</S>

Just checked that and the formatting is needed so I uncommented it.
Code: 
Option Explicit
 
Sub TranEnergyTime()
Dim wsSrc As Worksheet
Dim wsDst As Worksheet
Dim rngDate As Range
Dim rngTimes As Range
Dim rngValues As Range
Dim rngDst As Range
Dim NoTimes As Long
 
    Set wsSrc = Worksheets("Sheet1")
 
    Set wsDst = Worksheets("Sheet2")
 
    Set rngDate = wsSrc.Range("A2")
 
    Set rngTimes = wsSrc.Range("C1", wsSrc.Cells(1, Columns.Count).End(xlToLeft))
 
    wsDst.Range("A1:C1") = Array("Date/Time", "Measured In", "Value")
 
    Set rngDst = wsDst.Range("A2")
 
    NoTimes = rngTimes.Columns.Count
 
    While rngDate.Value <> ""
 
        rngDst.Resize(NoTimes) = rngDate
 
        rngDst.Resize(NoTimes).Offset(, 1) = rngDate.Offset(, 1)
 
        Set rngValues = rngDate.Offset(, 2).Resize(, NoTimes)
 
        rngTimes.Copy
 
        rngDst.PasteSpecial xlValues, xlPasteSpecialOperationAdd, Transpose:=True
 
        rngDst.Resize(NoTimes).NumberFormat = "dd/mm/yyyy hh:mm"
 
        rngValues.Copy
 
        rngDst.Offset(, 2).PasteSpecial xlValues, Transpose:=True
 
        Set rngDate = rngDate.Offset(1)
 
        Set rngDst = rngDst.Offset(NoTimes)
 
    Wend
 
    rngDst.Resize(, 3).EntireColumn.AutoFit
 
End Sub

PS it adds the measured in unit(?) for each row - wasn't sure if that's what you wanted.
Click to expand...


I get a runtime error at:

rngDst.Resize(NoTimes) = rngDate
 
Upvote 0
LLCoolDL said:
I'm getting an error message at CODE:

TimeAry(j - 2) = Cells(1, j)

The error is type mismatch???
Click to expand...
I guess your time cells are stored as strings instead of date/time. Try:


Code: 
Public Sub LLCoolDL()
Dim tempdte             As Date, _
    tempmeas            As String, _
    TimeAry(1 To 48)    As String, _
    TempVal(1 To 48)    As Long, _
    i                   As Long, _
    j                   As Long, _
    LR                  As Long
 
LR = Range("A" & Rows.Count).End(xlUp).Row
For j = 3 To 50
    TimeAry(j - 2) = Cells(1, j).Text
Next j
For i = LR To 2 Step -1
    tempdte = Range("A" & i).Value
    tempmeas = Range("B" & i).Value
    For j = 3 To 50
        TempVal(j - 2) = Cells(i, j).Value
    Next j
    Rows(i).Delete
    Rows(i & ":" & i + 47).Insert Shift:=xlDown
    Range(Cells(i, 1), Cells(i + 47, 1)).Value = tempdte
    Range("B" & i).Value = tempmeas
    For j = 1 To 48
        Cells(i + j - 1, 3).Value = TimeAry(j)
        Cells(i + j - 1, 4).Value = TempVal(j)
    Next j
Next i
End Sub
 
Upvote 0
MrKowz said:
I guess your time cells are stored as strings instead of date/time. Try:


Code: 
Public Sub LLCoolDL()
Dim tempdte             As Date, _
    tempmeas            As String, _
    TimeAry(1 To 48)    As String, _
    TempVal(1 To 48)    As Long, _
    i                   As Long, _
    j                   As Long, _
    LR                  As Long
 
LR = Range("A" & Rows.Count).End(xlUp).Row
For j = 3 To 50
    TimeAry(j - 2) = Cells(1, j).Text
Next j
For i = LR To 2 Step -1
    tempdte = Range("A" & i).Value
    tempmeas = Range("B" & i).Value
    For j = 3 To 50
        TempVal(j - 2) = Cells(i, j).Value
    Next j
    Rows(i).Delete
    Rows(i & ":" & i + 47).Insert Shift:=xlDown
    Range(Cells(i, 1), Cells(i + 47, 1)).Value = tempdte
    Range("B" & i).Value = tempmeas
    For j = 1 To 48
        Cells(i + j - 1, 3).Value = TimeAry(j)
        Cells(i + j - 1, 4).Value = TempVal(j)
    Next j
Next i
End Sub
Click to expand...


Error type mismatch on line:

tempdte = Range("A" & i).Value

Are there any good books to teach me visual basic? I've done all the online tutuorials I can find and that all makes perfect sense but it still hasn't helped me wiht this problem!
 
Upvote 0
Again, a formatting issue. Use this adjusted code:

Code: 
Public Sub LLCoolDL()
Dim tempdte             As String, _
    tempmeas            As String, _
    TimeAry(1 To 48)    As String, _
    TempVal(1 To 48)    As Long, _
    i                   As Long, _
    j                   As Long, _
    LR                  As Long
 
LR = Range("A" & Rows.Count).End(xlUp).Row
For j = 3 To 50
    TimeAry(j - 2) = Cells(1, j).Text
Next j
For i = LR To 2 Step -1
    tempdte = Range("A" & i).Text
    tempmeas = Range("B" & i).Value
    For j = 3 To 50
        TempVal(j - 2) = Cells(i, j).Value
    Next j
    Rows(i).Delete
    Rows(i & ":" & i + 47).Insert Shift:=xlDown
    Range(Cells(i, 1), Cells(i + 47, 1)).Value = tempdte
    Range("B" & i).Value = tempmeas
    For j = 1 To 48
        Cells(i + j - 1, 3).Value = TimeAry(j)
        Cells(i + j - 1, 4).Value = TempVal(j)
    Next j
Next i
End Sub
 
Upvote 0
And Again:

TempVal(j - 2) = Cells(i, j).Value

MrKowz said:
Again, a formatting issue. Use this adjusted code:

Code: 
Public Sub LLCoolDL()
Dim tempdte             As String, _
    tempmeas            As String, _
    TimeAry(1 To 48)    As String, _
    TempVal(1 To 48)    As Long, _
    i                   As Long, _
    j                   As Long, _
    LR                  As Long
 
LR = Range("A" & Rows.Count).End(xlUp).Row
For j = 3 To 50
    TimeAry(j - 2) = Cells(1, j).Text
Next j
For i = LR To 2 Step -1
    tempdte = Range("A" & i).Text
    tempmeas = Range("B" & i).Value
    For j = 3 To 50
        TempVal(j - 2) = Cells(i, j).Value
    Next j
    Rows(i).Delete
    Rows(i & ":" & i + 47).Insert Shift:=xlDown
    Range(Cells(i, 1), Cells(i + 47, 1)).Value = tempdte
    Range("B" & i).Value = tempmeas
    For j = 1 To 48
        Cells(i + j - 1, 3).Value = TimeAry(j)
        Cells(i + j - 1, 4).Value = TempVal(j)
    Next j
Next i
End Sub
Click to expand...
 
Upvote 0
We'll just treat everything as a string then (did you import this data from somewhere else? I wouldn't think that all of these values would be stored as strings):

Code: 
Public Sub LLCoolDL()
Dim tempdte             As String, _
    tempmeas            As String, _
    TimeAry(1 To 48)    As String, _
    TempVal(1 To 48)    As String, _
    i                   As Long, _
    j                   As Long, _
    LR                  As Long
 
LR = Range("A" & Rows.Count).End(xlUp).Row
For j = 3 To 50
    TimeAry(j - 2) = Cells(1, j).Text
Next j
For i = LR To 2 Step -1
    tempdte = Range("A" & i).Text
    tempmeas = Range("B" & i).Value
    For j = 3 To 50
        TempVal(j - 2) = Cells(i, j).Text
    Next j
    Rows(i).Delete
    Rows(i & ":" & i + 47).Insert Shift:=xlDown
    Range(Cells(i, 1), Cells(i + 47, 1)).Value = tempdte
    Range("B" & i).Value = tempmeas
    For j = 1 To 48
        Cells(i + j - 1, 3).Value = TimeAry(j)
        Cells(i + j - 1, 4).Value = TempVal(j)
    Next j
Next i
End Sub
 
Upvote 0
Yes it's imported from our electricity company.

Realised why it's not working.

The dates are in column E, the data is in column G... I'm trying to adjust your macro myself.
 
Upvote 0
If you want to change the code I posted:
Code: 
Set rngDate = wsSrc.Range("E2")
That should be it - the data still appears to be in the same place relative to the date column, ie 2 columns to the right.

Oops, actually needs another small change:
Code: 
    Set rngDate = wsSrc.Range("E2")
 
    Set rngTimes = wsSrc.Range("G1", wsSrc.Cells(1, Columns.Count).End(xlToLeft))
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
How to drag autofill from a set location a variable amount of cells away
2
Replies
15
Views
433
Peter_SSs
Peter_SSs
D
  • Solved
AutoFill or Copy Formula to All Relevant Cells
Replies
5
Views
784
deZine
D
S
  • Solved
VBA autofilling code
Replies
6
Views
571
Peter_SSs
Peter_SSs
T
  • Solved
Autofill formula via VBA erroring
Replies
3
Views
372
Alex Blakenburg
A
VinceF
  • Solved
Runtime Error 1004 with Macro
Replies
3
Views
401
Joe4
Joe4

Forum statistics

Threads
1,224,527
Messages
6,179,348
Members
452,907
Latest member
Roland Deschain

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top