Run-time error '1004' Name syntax incorrect

[tminhv]

What does this error mean when I try to run the code? Column A and B are new, and meet criteria. I've tried column B with the full formula, = sign included, and without, as well as with ' in front to show as a string.

View attachment 94442View attachment 94441

I also keep getting this error as well

 

[Joe4]

Welcome to the Board!

You have the "sh" in the wrong place. Try this:

Set rng = sh.Cells(r, "B")

 

[MARK858]

It should just be

Set rng = sh.Cells(r, "B")

don't wrap it within Range as Cells is already a range.

 

[tminhv]

Thanks that seemed to add it in the name manager, but it refers directly to the cell instead of taking the range inside the cell as the reference.

What's showing as the value is what i want to refer to

 

[MARK858]

Deleted as wouldn't work with the sheet name in the string

 

[MARK858]

Dim MyStr As String, rng as range

MyStr = sh.Cells(r, "B").Value
Set rng = Range(MyStr)

 

[tminhv]

not sure why that didn't work, but if i use my original code, and have it display as a string, '=xxx!$x$#:$x$#, it works now, thank you for your help

 

[MARK858]

Happy that you got it working