naming a range via vba

[thomas donino]

The following is breaking at the activeworkbook.Names.......... line

'set up the info area
Dim ranInfoArea As Range
Set ranInfoArea = Range("H1:J1")
ActiveWorkbook.Names.Add Name:="InfoArea", RefersTo:="=" &
Worksheets("Test").Range("H1:J1")
With Range("InfoArea")
.Merge True
.Font.Name = "Calabri"
.Font.Size = 9
.Font.Bold = True
.Font.Color = RGB(0, 0, 0)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With

 

[Jacob Skaria]

Change the Add line to the below and make sure you have a worksheet named
'test' in your activeworkbook

ActiveWorkbook.Names.Add Name:="InfoArea",
RefersTo:=Worksheets("Test").Range("H1:J1")

If this post helps click Yes

 

[thomas donino]

Jacob,

Thank you that worked fine. I have another problem in that this code is
generating a little square after each text string returned by the vlookup


strCommSym = Right(ranTarg, Len(ranTarg) - InStr(ranTarg, "."))
strCommName = WorksheetFunction.VLookup(strCommSym, tabRange, 2, False)
strPitHrs = WorksheetFunction.VLookup(strCommSym, tabRange, 3, False)

strFill = "Query for " & strCommName
strFill = strFill & vbCrLf & strPitHrs
strFill = strFill & vbCrLf & "This Query "

i guess I could trim the square, if thats even doable but I am more
concerned with why and how its there.

When I copy and paste the text in the cell to something else its fine and
shows like this;

"Query for SP500
9:30-4:15 EST
This Query "

but in the cell you see a little square after the SP500 and the EST, those
strings are returned from the vlookups.

td

 

[Jacob Skaria]

That represents vbCr. Use vbLF instead (as below) which will wrap the text
contents to the cell..

strFill = strFill & vbLf & strPitHrs
strFill = strFill & vbLf & "This Query "

If this post helps click Yes