Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-02-2021, 08:36 PM
trevorc trevorc is offline Intersect(Target, Sh.Range("AN4:AN5000")) can i reference this range another way? Windows 7 32bit Intersect(Target, Sh.Range("AN4:AN5000")) can i reference this range another way? Office 2013
Competent Performer
Intersect(Target, Sh.Range("AN4:AN5000")) can i reference this range another way?
 
Join Date: Jan 2017
Posts: 149
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default 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

Reply With Quote
  #2  
Old 03-02-2021, 08:43 PM
trevorc trevorc is offline Intersect(Target, Sh.Range("AN4:AN5000")) can i reference this range another way? Windows 7 32bit Intersect(Target, Sh.Range("AN4:AN5000")) can i reference this range another way? Office 2013
Competent Performer
Intersect(Target, Sh.Range("AN4:AN5000")) can i reference this range another way?
 
Join Date: Jan 2017
Posts: 149
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

Sorted, i can just give the column a reference and use that, works while in the table.
Reply With Quote
  #3  
Old 04-30-2021, 05:05 AM
Debaser's Avatar
Debaser Debaser is offline Intersect(Target, Sh.Range("AN4:AN5000")) can i reference this range another way? Windows 7 64bit Intersect(Target, Sh.Range("AN4:AN5000")) can i reference this range another way? Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 173
Debaser is on a distinguished road
Default

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
for example.
Reply With Quote
Reply

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
Intersect(Target, Sh.Range("AN4:AN5000")) can i reference this range another way? 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
Intersect(Target, Sh.Range("AN4:AN5000")) can i reference this range another way? Workarounds for the find "dynamic" range bug in VBA? Robert K S Word VBA 2 11-07-2016 01:00 PM
Intersect(Target, Sh.Range("AN4:AN5000")) can i reference this range another way? Making cross-reference say "Fig." instead of "Figure" dgalb Word 17 11-09-2014 06:25 AM
Intersect(Target, Sh.Range("AN4:AN5000")) can i reference this range another way? Mysterious "Subscript out of range" error rnstewart Excel 4 12-29-2005 01:04 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 07:16 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft