Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error
Hello,
Any ideas on the type misatch error I recieve here? Sub Autofill() LastRow = Range("E").End(xlUp) Set Range1 = Range(Range("A2"), Range("D65536").End(xlUp).Offset(1, 0)) Range1.Select Selection.Autofill Destination:=("Range1" & LastRow) End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error
I am not sure what you are trying to do. I modified the LastRow line, but
the Autofill might still give you a problem. Sub Autofill() lastRow = Cells(Rows.Count, "E").End(xlUp).Row Set Range1 = Range(Range("A2"), Range("D65536").End(xlUp).Offset(1, 0)) Range1.Select Selection.Autofill Destination:=("Range1" & lastRow) End Sub "el dee" wrote in message ... Hello, Any ideas on the type misatch error I recieve here? Sub Autofill() LastRow = Range("E").End(xlUp) Set Range1 = Range(Range("A2"), Range("D65536").End(xlUp).Offset(1, 0)) Range1.Select Selection.Autofill Destination:=("Range1" & LastRow) End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error
I think I've got most of it, but I don't know what you want to do with the
last line, so can't give a suggestion Option Explicit Sub test() Dim aWS As Excel.Worksheet Dim LastRow As Long Dim Range1 As Excel.Range Dim myRange As Excel.Range Set aWS = ActiveSheet LastRow = aWS.Cells(aWS.Rows.Count, 5).End(xlUp).Row Set myRange = aWS.Range("D" & aWS.Rows.Count).End(xlUp).Offset(1, 0) Set Range1 = aWS.Range(aWS.Range("A2"), myRange) Range1.Select 'v~~get an error here now. Not sure what you want to do Selection.AutoFill Destination:=("Range1" & LastRow) End Sub HTH, Barb Reinhardt "el dee" wrote: Hello, Any ideas on the type misatch error I recieve here? Sub Autofill() LastRow = Range("E").End(xlUp) Set Range1 = Range(Range("A2"), Range("D65536").End(xlUp).Offset(1, 0)) Range1.Select Selection.Autofill Destination:=("Range1" & LastRow) End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error
Hi Barb,
Thanks for taking a look. The last line is where I recieve the error. The Macro is supposed to select the last filled cells in columns A:D and auto fill to end of filled cells in column "E" Thanks again...! Still working on it... LastRow = Range("E").End(xlUp) Set Range1 = Range(Range("A2"), Range("D65536").End(xlUp).Offset(1, 0)) Range1.Select Selection.Autofill Destination:=("Range1" & LastRow) The last Line "Barb Reinhardt" wrote: I think I've got most of it, but I don't know what you want to do with the last line, so can't give a suggestion Option Explicit Sub test() Dim aWS As Excel.Worksheet Dim LastRow As Long Dim Range1 As Excel.Range Dim myRange As Excel.Range Set aWS = ActiveSheet LastRow = aWS.Cells(aWS.Rows.Count, 5).End(xlUp).Row Set myRange = aWS.Range("D" & aWS.Rows.Count).End(xlUp).Offset(1, 0) Set Range1 = aWS.Range(aWS.Range("A2"), myRange) Range1.Select 'v~~get an error here now. Not sure what you want to do Selection.AutoFill Destination:=("Range1" & LastRow) End Sub HTH, Barb Reinhardt "el dee" wrote: Hello, Any ideas on the type misatch error I recieve here? Sub Autofill() LastRow = Range("E").End(xlUp) Set Range1 = Range(Range("A2"), Range("D65536").End(xlUp).Offset(1, 0)) Range1.Select Selection.Autofill Destination:=("Range1" & LastRow) End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error
Thanks JLGWhiz. You are right, I am still recieving an error with the
autofill. I am trying to get the last cells with data in columns "A:D" to autofill to the end of data in column "E", which is already filled. "JLGWhiz" wrote: I am not sure what you are trying to do. I modified the LastRow line, but the Autofill might still give you a problem. Sub Autofill() lastRow = Cells(Rows.Count, "E").End(xlUp).Row Set Range1 = Range(Range("A2"), Range("D65536").End(xlUp).Offset(1, 0)) Range1.Select Selection.Autofill Destination:=("Range1" & lastRow) End Sub "el dee" wrote in message ... Hello, Any ideas on the type misatch error I recieve here? Sub Autofill() LastRow = Range("E").End(xlUp) Set Range1 = Range(Range("A2"), Range("D65536").End(xlUp).Offset(1, 0)) Range1.Select Selection.Autofill Destination:=("Range1" & LastRow) End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error
Maybe...
Option Explicit Sub testme() Dim LastRow As Long Dim RngToCopy As Range Dim HowManyRows As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row 'using the last used cell in column A to get the row to copy Set RngToCopy = .Cells(.Rows.Count, "A").End(xlUp) HowManyRows = LastRow - RngToCopy.Row + 1 If HowManyRows 1 Then RngToCopy.Resize(HowManyRows, 4).FillDown End If End With End Sub el dee wrote: Hello, Any ideas on the type misatch error I recieve here? Sub Autofill() LastRow = Range("E").End(xlUp) Set Range1 = Range(Range("A2"), Range("D65536").End(xlUp).Offset(1, 0)) Range1.Select Selection.Autofill Destination:=("Range1" & LastRow) End Sub -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error
Let's try to cake care of it this time...
The following macro will Fill Down Values in Column A, with Some Data Already in Column A, Based on Number of Rows Filled in Column A. Sub AutoFill() Dim CountRows As Double Dim Iloop As Double 'Turn off warnings, etc. Application.ScreenUpdating = False Application.DisplayAlerts = False CountRows = Cells(Rows.Count, "E").End(xlUp).Row For Iloop = 2 To CountRows If IsEmpty(Cells(Iloop, "A")) Then Cells(Iloop, "A") = Cells(Iloop - 1, "A") End If Next Iloop 'Turn on warnings, etc. Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub Another sample... Fill Down in Column D, Based on Used Range in Column E: Sub fill() Dim lastrow As Long lastrow = Worksheets("Sheet1").Cells(Rows.Count, "E").End(xlUp).Row Range("D2").AutoFill Range("D2:D" & lastrow) End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Dave Peterson" wrote: Maybe... Option Explicit Sub testme() Dim LastRow As Long Dim RngToCopy As Range Dim HowManyRows As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row 'using the last used cell in column A to get the row to copy Set RngToCopy = .Cells(.Rows.Count, "A").End(xlUp) HowManyRows = LastRow - RngToCopy.Row + 1 If HowManyRows 1 Then RngToCopy.Resize(HowManyRows, 4).FillDown End If End With End Sub el dee wrote: Hello, Any ideas on the type misatch error I recieve here? Sub Autofill() LastRow = Range("E").End(xlUp) Set Range1 = Range(Range("A2"), Range("D65536").End(xlUp).Offset(1, 0)) Range1.Select Selection.Autofill Destination:=("Range1" & LastRow) End Sub -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error
You have a lot of assumptions in this reply.
I wonder how many apply to the OP's question. ryguy7272 wrote: Let's try to cake care of it this time... The following macro will Fill Down Values in Column A, with Some Data Already in Column A, Based on Number of Rows Filled in Column A. Sub AutoFill() Dim CountRows As Double Dim Iloop As Double 'Turn off warnings, etc. Application.ScreenUpdating = False Application.DisplayAlerts = False CountRows = Cells(Rows.Count, "E").End(xlUp).Row For Iloop = 2 To CountRows If IsEmpty(Cells(Iloop, "A")) Then Cells(Iloop, "A") = Cells(Iloop - 1, "A") End If Next Iloop 'Turn on warnings, etc. Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub Another sample... Fill Down in Column D, Based on Used Range in Column E: Sub fill() Dim lastrow As Long lastrow = Worksheets("Sheet1").Cells(Rows.Count, "E").End(xlUp).Row Range("D2").AutoFill Range("D2:D" & lastrow) End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Dave Peterson" wrote: Maybe... Option Explicit Sub testme() Dim LastRow As Long Dim RngToCopy As Range Dim HowManyRows As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row 'using the last used cell in column A to get the row to copy Set RngToCopy = .Cells(.Rows.Count, "A").End(xlUp) HowManyRows = LastRow - RngToCopy.Row + 1 If HowManyRows 1 Then RngToCopy.Resize(HowManyRows, 4).FillDown End If End With End Sub el dee wrote: Hello, Any ideas on the type misatch error I recieve here? Sub Autofill() LastRow = Range("E").End(xlUp) Set Range1 = Range(Range("A2"), Range("D65536").End(xlUp).Offset(1, 0)) Range1.Select Selection.Autofill Destination:=("Range1" & LastRow) End Sub -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error
Thanks Dave,
Worked perfectly! Very nicely done. "Dave Peterson" wrote: You have a lot of assumptions in this reply. I wonder how many apply to the OP's question. ryguy7272 wrote: Let's try to cake care of it this time... The following macro will Fill Down Values in Column A, with Some Data Already in Column A, Based on Number of Rows Filled in Column A. Sub AutoFill() Dim CountRows As Double Dim Iloop As Double 'Turn off warnings, etc. Application.ScreenUpdating = False Application.DisplayAlerts = False CountRows = Cells(Rows.Count, "E").End(xlUp).Row For Iloop = 2 To CountRows If IsEmpty(Cells(Iloop, "A")) Then Cells(Iloop, "A") = Cells(Iloop - 1, "A") End If Next Iloop 'Turn on warnings, etc. Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub Another sample... Fill Down in Column D, Based on Used Range in Column E: Sub fill() Dim lastrow As Long lastrow = Worksheets("Sheet1").Cells(Rows.Count, "E").End(xlUp).Row Range("D2").AutoFill Range("D2:D" & lastrow) End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Dave Peterson" wrote: Maybe... Option Explicit Sub testme() Dim LastRow As Long Dim RngToCopy As Range Dim HowManyRows As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row 'using the last used cell in column A to get the row to copy Set RngToCopy = .Cells(.Rows.Count, "A").End(xlUp) HowManyRows = LastRow - RngToCopy.Row + 1 If HowManyRows 1 Then RngToCopy.Resize(HowManyRows, 4).FillDown End If End With End Sub el dee wrote: Hello, Any ideas on the type misatch error I recieve here? Sub Autofill() LastRow = Range("E").End(xlUp) Set Range1 = Range(Range("A2"), Range("D65536").End(xlUp).Offset(1, 0)) Range1.Select Selection.Autofill Destination:=("Range1" & LastRow) End Sub -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
runtime error 13 - type mismatch error in Excel 97 on Citrix | Excel Programming | |||
Conditional Formatting - Run Time Error '13' Type Mismatch Error | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming |