Macro to name a range

[mrichey]

Using the example below, how can I create a macro to select everything from the server name in A1 (BOMDirector04) to B12(cell to right of "Done")and name the range selection to match the server name from A1?

Thanks much for any suggestions!!

A B
BOMDirector04
Collection Monitor
---------- -------
w2k_Memory CommittedBytes
Universal ncustom: c:/tivoli/scripts/cleanperf.pl
w2k_NetworkInterface OutputQueLen
w2k_Memory PgFltsPerSec
w2k_Memory PagesPerSec
w2k_PhysicalDisk PrcDskTime
w2k_Processor PrcIntTime
w2k_Processor IntsPerSec
Done
BOMPORTAL01
Collection Monitor
---------- -------
Universal appStatus
w2k_System SysCallsPerSec
w2k_Processor PrcPrivTime
w2k_Processor PrcUsrTime
w2k_Processor PrcUsrTime
Done
BOMPORTAL02
Collection Monitor
---------- -------
Universal appStatus
w2k_System SysCallsPerSec
w2k_Processor PrcUsrTime
Done

 

[SkipVought]

Hi,

Is the range that you want, static or will it be dynamic based on data entered in columns A & B?

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[mrichey]

Hi Skip, thanks for responding.
The range will be dynamic. The process would be something like:
1. Find Collection
2. Using relative reference, move to cell above, copy the data (should always be the server name)
3. Select all data from the server name field (where cursor should now be) to the first occurence of the word "Done" (number of lines will vary), and over to the b column.

A B C
1 Server xxx
2 xxx xxx
3 xxx xxx
4 Done xxx

4. After that range is selected, move up to the cell name field and name the range by pasting in the copied data.
5. The really tricky part to me, go back to the above referenced "Done" location and search again for "Collection", move up and copy data in THAT cell, and repeat the process.

Does this make sense? Thanks!!

 

[SkipVought]

    Dim r1 As Range, r2 As Range, lRowPrev As Long
    Set r1 = Cells.Find("Collection")
    Set r2 = Cells.Find("Done")
    lRowPrev = r1 - 1
    Do While r1.Row > lRowPrev
      ActiveWorkbook.Names.Add _
        Name:=r1.Offset(-1).Value, _
        RefersTo:=Range(Cells(r1, 1), Cells(r2, 2))
      Set r1 = Cells.Find("Collection", r1)
      Set r2 = Cells.Find("Done", r2)
      lRowPrev = r1.Row
    Loop

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[SkipVought]

ooops, sorry!

    Dim r1 As Range, r2 As Range, lRowPrev As Long
    Set r1 = Cells.Find("Collection")
    Set r2 = Cells.Find("Done")
    lRowPrev = r1 - 1
    Do While r1.Row > lRowPrev
      ActiveWorkbook.Names.Add _
        Name:=r1.Offset(-1).Value, _
        RefersTo:=Range(Cells(r1, 1), Cells(r2, 2))
      lRowPrev = r1.Row
      Set r1 = Cells.Find("Collection", r1)
      Set r2 = Cells.Find("Done", r2)
    Loop

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[mrichey]

Skip, when I paste this into a macro, I get a runtime error (13) Type Mismatch error on the "1RowPrev - r1 -1" line. Any suggestions! Thanks.

Sub test()
'
' test Macro
' Macro recorded 4/24/2005 by Mark Richey
'
Dim r1 As Range, r2 As Range, lRowPrev As Long
Set r1 = Cells.Find("Collection")
Set r2 = Cells.Find("Done")
lRowPrev = r1 - 1
Do While r1.Row > lRowPrev
ActiveWorkbook.Names.Add _
Name:=r1.Offset(-1).Value, _
RefersTo:=Range(Cells(r1, 1), Cells(r2, 2))
lRowPrev = r1.Row
Set r1 = Cells.Find("Collection", r1)
Set r2 = Cells.Find("Done", r2)
Loop

 

[PHV]

Replace this:
lRowPrev = r1 - 1
By this:
lRowPrev = r1.Row - 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244