Formula not working

[Kurt]

I have the following line of code:

'TopLeft.Offset(1, 17).Resize(RowCount, 1).Value = Projectinfo.Range_BidName

I am trying to get it to see this instead but the code stops.

AddFormula TopLeft.Offset(1,17).Resize(RowCount, 1), "=ProjectInfo.BidName"

I am trying to use the Range BidName instead of Cell C6.

I am trying to use the value from the Worksheet ProjectInfo Cell C6.

 

[Col Delane]

I am trying to use the Range BidName instead of Cell C6.

I am trying to use the value from the Worksheet ProjectInfo Cell C6.

You can't refer to Defined Names in VBA without telling the VBE what type of Name it is, so you either:

1. need to qualify the Name with its related object (e.g. Range) - like "Range("TopLeft")", or
2. declare a VBA variable in your code (give the varable a name and declare its object type) with a statement like "Dim MyRange as Range", and then assign (initialise) that variable with the specific Excel object your targeting with "Set MyRange = Range"TopLeft")".

Note that to highlight the code elements I've enclosed them in double quotes and colored them blue, but that double quotes are actually used in the code around the Names.

'TopLeft.Offset(1, 17).Resize(RowCount, 1).Value = Projectinfo.Range_BidName

From the above I'm assuming that:

1. "TopLeft" and "RowCOunt" are also Named Ranges.
2. Both the "TopLeft" and "BidName" Names have Workbook scope (can have only one such Name in the workbook, and can be referred to without qualification anywhere in the workbook - like "=TopLeft") rather than be specific to a particular worksheet (= sheet scope, which allows you to have the same name on multiple sheets, but must have the sheet name qualification a a prefix when referred to in formulas - like "=Projectinfo!BidName")

AddFormula TopLeft.Offset(1,17).Resize(RowCount, 1), "=ProjectInfo.BidName"

I'm not clear as to what you're trying to do here:

• Are you targetting just one cell or a range with "TopLeft.Offset(1,17).Resize(RowCount, 1)"?

Subject to the above question, your code needs to be something like this:

Range("TopLeft").Offset(1,17).Resize(Range("RowCount"), 1).Formula = "=BidName"