Solved

Created ranges and name them

Posted on 2011-02-25
2
195 Views
Last Modified: 2012-05-11
Dear Experts:

this nice code, courtesy by rorya, performs the following task ...
(1) looking for the word 'Sales' in the current worksheet,
(2) if found, creates a table with rngFound.CurrentRegion
(3) assigns/applies names and stylesfor these ListObjects
(4) looping

I would like to have a second version of this code ...
... where the created ranges 'just' get named with the following naming convention: range1, range2, range3 and no 'ListObjects' are created.

I hope this is feasible.

Thank you very much in advance. Regards, Andreas

Sub CreateSalesTables()
   Dim rngFound As Range
   Dim strAddy As String
   Dim lngCounter As Long
   Set rngFound = ActiveSheet.UsedRange.Find(what:="Sales", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
   If Not rngFound Is Nothing Then
      lngCounter = 1
      strAddy = rngFound.Address
      Do
         CreateTable rngFound.CurrentRegion, "Table" & lngCounter
         lngCounter = lngCounter + 1
         Set rngFound = ActiveSheet.UsedRange.FindNext(rngFound)
      Loop While rngFound.Address <> strAddy
   End If
End Sub

Sub CreateTable(rng As Range, strName As String)
    With rng.Worksheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
      .Name = strName
        'No go in 2003
      .TableStyle = "TableStyleLight2"
   End With

End Sub

Open in new window

0
Comment
Question by:AndreasHermle
2 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 34979837
As I said before:
Sub CreateSalesTables()
   Dim rngFound As Range
   Dim strAddy As String
   Dim lngCounter As Long
   Set rngFound = ActiveSheet.UsedRange.Find(what:="Sales", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
   If Not rngFound Is Nothing Then
      lngCounter = 1
      strAddy = rngFound.Address
      Do
         rngFound.CurrentRegion.Name =,"Range" & lngCounter
         lngCounter = lngCounter + 1
         Set rngFound = ActiveSheet.UsedRange.FindNext(rngFound)
      Loop While rngFound.Address <> strAddy
   End If
End Sub

Open in new window


:)
0
 

Author Closing Comment

by:AndreasHermle
ID: 34980053
Rorya:
great. Thank you very much for your excellent and swift help.

Regards, Andreas
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now