Can't concatenate a cell address

**JohnSidney** · 03-12-2010, 07:22 AM

Hiya,

This seems odd. I want to concatenate a cell address but I get an error: Run-time error '1404'; Method 'Range' of object '_Global' failed.

here's my code:
dim intCount as integer

intCount = 2
strCellValue = Range("A" + Str(intCount)).Value 'This throws the error

strCellValue = Range("A2").Value 'This works!

I tried changing my variable type to variant but no such luck. Any ideas?

Cheers,
John

**JohnSidney** · 03-12-2010, 07:37 AM

Ah, for some reason I had to use trim. Null terminator maybe?

strCellValue = Range("A" + Trim(Str(intCount))).Value

Cheers,
J

Originally Posted by JohnSidney

Hiya,

This seems odd. I want to concatenate a cell address but I get an error: Run-time error '1404'; Method 'Range' of object '_Global' failed.

here's my code:
dim intCount as integer

intCount = 2
strCellValue = Range("A" + Str(intCount)).Value 'This throws the error

strCellValue = Range("A2").Value 'This works!

I tried changing my variable type to variant but no such luck. Any ideas?

Cheers,
John

**Andy Pope** · 03-12-2010, 08:20 AM

FYI, here is what the help says about the STR function.

Remarks

When numbers are converted to strings, a leading space is always reserved for the sign of number. If number is positive, the returned string contains a leading space and the plus sign is implied.

Use the Format function to convert numeric values you want formatted as dates, times, or currency or in other user-defined formats. Unlike Str, the Format function doesn't include a leading space for the sign of number.

Note The Str function recognizes only the period (.) as a valid decimal separator. When different decimal separators may be used (for example, in international applications), use CStr to convert a number to a string.

So your problem is leading space.
Try this in the immediate window to see the difference.

?">A" + Str(1);"<",">A";CStr(1);"<"

**rwgrietveld** · 03-12-2010, 08:28 AM

Maybe to obvious but my eye spotted:
"A" + Str(intCount)

Should this be "A" & Str(intCount) ?

maybe even use CStr() for the type casting/conversion

**Andy Pope** · 03-12-2010, 08:32 AM

Usually the & is used for concatenation although + will work.

Just becareful when using the + as diffirent variable types can have different results.