naming a range via vba

  • Thread starter Thread starter thomas donino
  • Start date Start date
T

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
 
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
 
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
 
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 "
Click to expand...


If this post helps click Yes
 
Back
Top