Name Defined 1

[icdastar]

Hi

I just joined this forum and this is the first time I've ever really used a forum so I'm not sure how this works. But can someone help me with trying to figure out how to make this macro, which I'm also new at since I've never really programmed before. Below is the code:

Dim s As Integer
Sheets("Instructions").Select
s = Range("C42").Value


Sheets("BB").Select
Range("A1").Select
Dim i As Integer
i = 0

Do Until i = s

Selection.End(xlDown).Select
Selection.CurrentRegion.Select
ActiveWorkbook.Names.Add Name:="Apple", RefersToR1C1:=- "=BB!R5C1:R23C5"
Selection.End(xlDown).Select
i = i + 1

Loop

So basically what I'm trying to do is create a macro that will select a current region and name define it then and continue until there are no current regions which is defined through "s". But I don't know how to get the name define to change from "apple" everytime it goes through the loop. Is it possible to make it reference a cell within the current region as a title instead of "apple".

Thanks!

IcDaStar

 

[VBAjedi]

Before I jump in and start answering your questions, I'd like to know what you are trying to accomplish. Could you describe what your spreadsheet is for and what you're trying to make it do? I suspect you may be going the long way around here. . .

VBAjedi

 

[icdastar]

Well I have an excel spreadsheet I receive every month and on it are sections that are separated by one blank row but the number of sections varies. Now for each section I want the macro to choose the cell that contains the title of the data set and name define it. The Instructions sheet defines "s" as the number of sections. In the example below "s" would be equal to 2. And the section name defined should be Apple and Orange.

Apple
Los Angeles 1 15
San Francisco 2 10
Las Vegas 3 5
New York 4 1

Orange
Los Angeles 1 15
San Francisco 2 10
Las Vegas 3 5
New York 4 1

Hope that makes sense..Thanks!

 

[SkipVought]

Hi,

Sub test()
    Dim s As Integer
    Sheets("Instructions").Select
    s = Range("C42").Value

    Sheets("BB").Select
    Set rng = Range("A1")
    Dim i As Integer
    i = 0
    
    Do Until i = s

      With rng.End(xlDown).CurrentRegion
        ActiveWorkbook.Names.Add _
          Name:=.Cells(1, 1).Value, _
          RefersTo:="=" & ActiveSheet.Name & "!" & .Address
        Set rng = .End(xlDown)
      End With
      i = i + 1
    
    Loop

End Sub

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[icdastar]

Thanks...I tried running it using the code but I it had a problem because rng was not defined so I added Dim rng as Range, but then I had another error when I got to the

ActiveWorkbook.Names.Add _
Name:=.Cells(1, 1).Value, _
RefersTo:="=" & ActiveSheet.Name & "!" & .Address

The error says "Run Time error '1004' That name is not valid. Now what do I do?

 

[SkipVought]

If you have Names with SPACES, you'll get that error

          Name:=Replace(.Cells(1, 1).Value," ","_"), _

This replaces space with underscore.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[icdastar]

Thank you sooo much! It worked =)

Just curious but how do you learn to program like this and understand all the errors?

Also how do you interpret this code

ActiveWorkbook.Names.Add _
Name:=Replace(.Cells(1, 1).Value, " ", "_"), _
RefersTo:="=" & ActiveSheet.Name & "!" & .Address

What does the "!" and "=" mean? Thanks just trying to learn...

 

[VBAjedi]

Well, the code is assuming that you have the desired name for the CurrentRegion range in the first cell (row 1, column 1) of the CurrentRegion range. Do you have a value in that cell, and is it a valid range name?

VBAjedi

 

[SkipVought]

[tt]
Apple
Los Angeles 1 15
San Francisco 2 10
Las Vegas 3 5
New York 4 1
[/tt]

      With rng.End(xlDown).CurrentRegion
        ActiveWorkbook.Names.Add _
          Name:=Replace(.Cells(1, 1).Value," ","_"), _
          RefersTo:="=" & ActiveSheet.Name & "!" & .Address
        Set rng = .End(xlDown)
      End With

1. rng.End(xlDown).CurrentRegion.Cells(1,1).Value is the top left cell of the contiguous range -- in the example above it is Apple
2. RefersTo needs an ADDRESS in the form of a spreadsheet reference like, =Sheet1!A5:C10


Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[icdastar]

Thanks for your help and explanation!

Now that I created these ranges is there a way to call each of the ranges and do a For Each statement? Or do I actually have to type For Range "Apples"...?

 

[SkipVought]

   Dim nm As Name
   For Each nm In ActiveWorkbook.Names
      With nm
      MsgBox "Name:" & .Name & ": Address:" & .RefersTo
      End With
   Next

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[icdastar]

Okay I'm getting an error. This is the code I'm using:

Dim nm As Name
For Each nm In ActiveWorkbook.Names

nm.Find("Atlanta").Select
ActiveCell.Offset(0, 2).Font.ColorIndex = 3
If ActiveCell.Value = 0 Is Nothing Then

Else
ActiveCell.Copy
Sheets("JE 600").Select
Range("E8").Select

If Range("E8").Value = " " Then
ActiveSheet.Paste
Application.CutCopyMode = False

Else
Range("E8").End(xlDown).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Next


The error I'm receiving is: Next Without For

I'm not sure why its saying that...

 

[VBAjedi]

icdastar,

Every If statement needs an End If at some point. So if you are trying to "nest" your two if statements, it would look like:

    Dim nm As Name
    For Each nm In ActiveWorkbook.Names
    
        nm.Find("Atlanta").Select
        ActiveCell.Offset(0, 2).Font.ColorIndex = 3
        If ActiveCell.Value = 0 Is Nothing Then
        
        Else
        ActiveCell.Copy
        Sheets("JE 600").Select
        Range("E8").Select
            
            If Range("E8").Value = " " Then
            ActiveSheet.Paste
            Application.CutCopyMode = False
            
            Else
            Range("E8").End(xlDown).Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
            End If
        End If
    Next

VBAjedi