|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Intersect(Target, Sh.Range("AN4:AN5000")) can i reference this range another way?
The problem I'm having is the range shown below "AN4:AN5000" is there any way to tell VBA this is a named range or selection. I can't add any columns to the sheet without changing a whole bunch of code. Only 1 example shown, I am using this in lots of columns.
P.S. the range is part of a large table. Code:
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) If Not Intersect(Target, Sh.Range("AN4:AN5000")) Is Nothing Then ActiveCell.FormulaR1C1 = "Date :- " & Date & " , " & Intersect(acrow, tblColumns("Company Name").Range) & " INV Number :- " & Intersect(acrow, tblColumns("Invoice Number").Range) & ", Repairs to " & Intersect(acrow, tblColumns("Part").Range) & " " & " S/N " & Format(Intersect(acrow, tblColumns("Serial Number").Range), "000000000000") & " On " & Intersect(acrow, tblColumns("RMA Number").Range) With ActiveCell fst = InStr(8, ActiveCell, ":") 'Format INV BOLD .Characters(fst + 3, ActiveCell.Characters.Count - 4).Font.Bold = True .Characters(fst + 7, ActiveCell.Characters.Count).Font.Bold = False 'Format INV number size .Characters(fst + 3, ActiveCell.Characters.Count - 4).Font.size = 20 .Characters(fst + 7, ActiveCell.Characters.Count).Font.size = 11 End With Exit Sub End If |
#2
|
|||
|
|||
Sorted, i can just give the column a reference and use that, works while in the table.
|
#3
|
||||
|
||||
If that refers to a column in an actual Table, you can refer to it as such using something like:
Code:
Me.Listobjects("table name").Listcolumns("column header").databodyrange |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Declare wBk as workbook. Set wBk = workbooks("NewBook.xls") gives error: Subscript out of range. | Syed Shabbir | Excel Programming | 1 | 09-27-2020 09:35 AM |
The "group or ungroup text range" control in the developer tab breaks my document up into many pages | damiansiniakowicz | Word | 8 | 05-10-2018 12:30 PM |
Workarounds for the find "dynamic" range bug in VBA? | Robert K S | Word VBA | 2 | 11-07-2016 01:00 PM |
Making cross-reference say "Fig." instead of "Figure" | dgalb | Word | 17 | 11-09-2014 06:25 AM |
Mysterious "Subscript out of range" error | rnstewart | Excel | 4 | 12-29-2005 01:04 PM |