Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range("C100:D200").Select with variable names | Excel Programming | |||
Variable names for named range | Excel Discussion (Misc queries) | |||
using a "variable" in range names | Excel Discussion (Misc queries) | |||
'ActiveWorkbook.Names.Add Name:' how to make range variable? | Excel Programming | |||
Macro in VBA: Setting a variable print range HELP NEEDED!! | Excel Programming |