Variable Names Range - Help Needed
Hi all,
I am using the code below to try and resize the range in case a record has been added to the bottom of this range. I am not sure if the "Old" range must first be deleted before adding it again. I get a "Run-Time error '13': Type mismatch" message when i run it at the point mentioned below. Any help much appreciated. Private Sub UserForm_Initialize() ' Dim wb As Workbook, sh As Worksheet Set wb = Workbooks("Logistics_Cost_TM_Input.xls") Set sh = Sheets("Part_Family") 'Application.Workbooks(wb).Names("Part_Family_Desc ription").Delete<= ?? '<<== ERROR MESSAGE AT THIS POINT ====== Workbooks(wb).Names.Add Name:="Part_Family_Description", _ RefersTo:=Workbooks(wb).Sheets("sh").Range("D3", Range("D65536").End(xlUp)) cmb_PrtFam.RowSource = "Part_Family!Part_Family_Description" End Sub -- Les |
Variable Names Range - Help Needed
Les,
Maybe this Dim rng As Range Set rng = Range("Part_Family_Description") lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row added = lastrow - rng.Rows.Count rng.Resize(rng.Rows.Count + added).Name = "Part_Family_Description" Mike "Les" wrote: Hi all, I am using the code below to try and resize the range in case a record has been added to the bottom of this range. I am not sure if the "Old" range must first be deleted before adding it again. I get a "Run-Time error '13': Type mismatch" message when i run it at the point mentioned below. Any help much appreciated. Private Sub UserForm_Initialize() ' Dim wb As Workbook, sh As Worksheet Set wb = Workbooks("Logistics_Cost_TM_Input.xls") Set sh = Sheets("Part_Family") 'Application.Workbooks(wb).Names("Part_Family_Desc ription").Delete<= ?? '<<== ERROR MESSAGE AT THIS POINT ====== Workbooks(wb).Names.Add Name:="Part_Family_Description", _ RefersTo:=Workbooks(wb).Sheets("sh").Range("D3", Range("D65536").End(xlUp)) cmb_PrtFam.RowSource = "Part_Family!Part_Family_Description" End Sub -- Les |
Variable Names Range - Help Needed
I should have added this will also reduce the size of the named range if
records are deleted Mike "Mike H" wrote: Les, Maybe this Dim rng As Range Set rng = Range("Part_Family_Description") lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row added = lastrow - rng.Rows.Count rng.Resize(rng.Rows.Count + added).Name = "Part_Family_Description" Mike "Les" wrote: Hi all, I am using the code below to try and resize the range in case a record has been added to the bottom of this range. I am not sure if the "Old" range must first be deleted before adding it again. I get a "Run-Time error '13': Type mismatch" message when i run it at the point mentioned below. Any help much appreciated. Private Sub UserForm_Initialize() ' Dim wb As Workbook, sh As Worksheet Set wb = Workbooks("Logistics_Cost_TM_Input.xls") Set sh = Sheets("Part_Family") 'Application.Workbooks(wb).Names("Part_Family_Desc ription").Delete<= ?? '<<== ERROR MESSAGE AT THIS POINT ====== Workbooks(wb).Names.Add Name:="Part_Family_Description", _ RefersTo:=Workbooks(wb).Sheets("sh").Range("D3", Range("D65536").End(xlUp)) cmb_PrtFam.RowSource = "Part_Family!Part_Family_Description" End Sub -- Les |
Variable Names Range - Help Needed
Private Sub UserForm_Initialize()
' Dim wb As Workbook, sh As Worksheet Set wb = Workbooks("Logistics_Cost_TM_Input.xls") Set sh = Sheets("Part_Family") wb.Names.Add Name:="Part_Family_Description", _ RefersTo:="='" & sh.Name & "'!" & Range(sh.Range("D3"), sh.Range("D65536").End(xlUp)).Address cmb_PrtFam.RowSource = "Part_Family!Part_Family_Description" End Sub -- __________________________________ HTH Bob "Les" wrote in message ... Hi all, I am using the code below to try and resize the range in case a record has been added to the bottom of this range. I am not sure if the "Old" range must first be deleted before adding it again. I get a "Run-Time error '13': Type mismatch" message when i run it at the point mentioned below. Any help much appreciated. Private Sub UserForm_Initialize() ' Dim wb As Workbook, sh As Worksheet Set wb = Workbooks("Logistics_Cost_TM_Input.xls") Set sh = Sheets("Part_Family") 'Application.Workbooks(wb).Names("Part_Family_Desc ription").Delete<= ?? '<<== ERROR MESSAGE AT THIS POINT ====== Workbooks(wb).Names.Add Name:="Part_Family_Description", _ RefersTo:=Workbooks(wb).Sheets("sh").Range("D3", Range("D65536").End(xlUp)) cmb_PrtFam.RowSource = "Part_Family!Part_Family_Description" End Sub -- Les |
Variable Names Range - Help Needed
Thanx Mike H
-- Les "Mike H" wrote: Les, Maybe this Dim rng As Range Set rng = Range("Part_Family_Description") lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row added = lastrow - rng.Rows.Count rng.Resize(rng.Rows.Count + added).Name = "Part_Family_Description" Mike "Les" wrote: Hi all, I am using the code below to try and resize the range in case a record has been added to the bottom of this range. I am not sure if the "Old" range must first be deleted before adding it again. I get a "Run-Time error '13': Type mismatch" message when i run it at the point mentioned below. Any help much appreciated. Private Sub UserForm_Initialize() ' Dim wb As Workbook, sh As Worksheet Set wb = Workbooks("Logistics_Cost_TM_Input.xls") Set sh = Sheets("Part_Family") 'Application.Workbooks(wb).Names("Part_Family_Desc ription").Delete<= ?? '<<== ERROR MESSAGE AT THIS POINT ====== Workbooks(wb).Names.Add Name:="Part_Family_Description", _ RefersTo:=Workbooks(wb).Sheets("sh").Range("D3", Range("D65536").End(xlUp)) cmb_PrtFam.RowSource = "Part_Family!Part_Family_Description" End Sub -- Les |
All times are GMT +1. The time now is 10:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com