How to assign a value as a Named Area

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

et - 11 Jun 2006 11:22 GMT

I would like to write a VBA to make some copies automatically. The range is
to be selected based on a cell's value.

For example :
if A1 = "General Admin" Then print area named "GA"
if A1 = "Sales Admin" Then print area named "SA"

My VBA is :
Dim x As String
If ActiveCell.Value = "General Management" Then
x = "GA"
ElseIf ActiveCell.Value = "Sales Admin." Then
x = "SA"
ElseIf ActiveCell.Value = "Beijing" Then
x = "BJ"
ElseIf ActiveCell.Value = "Shanghai" Then
x = "SH"
End If

Range(x).Select

I guess I chose the wrong data type but not sure which one is correct. Could
anyone help, thanks.

et

Reply to this Message

Dave Peterson - 11 Jun 2006 14:01 GMT

Sometimes when you get a long list of things to check, it's easier to use the
"select case/end select" structure. I think it makes it a little easier to
read.

Option Explicit
Sub testme()

Dim x As String

With ActiveSheet
Select Case LCase(.Range("a1").Value)
Case Is = LCase("general management"): x = "ga"
Case Is = LCase("Sales Admin"): x = "sa"
Case Is = lCase("Beijing"): x = "bj"
Case Is = LCase("Shanghai"): x = "sh"
Case Else
x = ""
End Select
If x = "" Then
'do nothing
Else
.Range(x).PrintOut preview:=True
End If
End With

End Sub

> I would like to write a VBA to make some copies automatically. The range is
> to be selected based on a cell's value.
[quoted text clipped - 21 lines]
>
> et

Signature

Dave Peterson

Reply to this Message

somethinglikeant - 11 Jun 2006 14:02 GMT

Sub PrintWhichArea()
Select Case [A1] 'chooses different print areas dependant on value of
A1
Case "General Management"
ActiveWorkbook.Names.Add Name:="Print_Area",
RefersToR1C1:=Range("GA")
Case "Sales Admin."
ActiveWorkbook.Names.Add Name:="Print_Area",
RefersToR1C1:=Range("SA")
Case "Beijing"
ActiveWorkbook.Names.Add Name:="Print_Area",
RefersToR1C1:=Range("BJ")
Case "Shanghai"
ActiveWorkbook.Names.Add Name:="Print_Area",
RefersToR1C1:=Range("SH")
End Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True 'prints
End Sub

somethinglikeant

> I would like to write a VBA to make some copies automatically. The range is
> to be selected based on a cell's value.
[quoted text clipped - 21 lines]
>
> et

Reply to this Message

Don Guillett - 11 Jun 2006 14:07 GMT

Try this instead. Change to suit your needs. Be sure to use ALL CAPS in the
case "ga" "GA"

Sub selectprintarea()
Select Case UCase(Range("a1"))
Case "GA": x = "GA"
Case "SA": x = "SA"
Case "BJ": x = "BJ"
Case "a": x = "sj"
Case Else
End Select
Range(x).PrintPreview
End Sub

Signature

Don Guillett
SalesAid Software

>I would like to write a VBA to make some copies automatically. The range is
>to be selected based on a cell's value.
[quoted text clipped - 21 lines]
>
> et

Reply to this Message