String Concatenation problems

[cbjohn1]

Sub buildCountries()
    Dim start As Range
    Dim gdp As Range
    Dim cb As Range
    Dim unemploy As Range
    Dim cpi As Range
    Dim countd As Integer
    Dim counts As Integer
    Dim ip As Range
    Set start = Sheet23.Range("B11")
    Set gdp = Sheet27.Range("C9")
    Set cb = Sheet24.Range("C9")
    Set unemploy = Sheet3.Range("C9")
    Set cpi = Sheet21.Range("C9")
    Set gdp = Sheet13.Range("C9")
    Set ip = Sheet2.Range("C9")
    countd = 0
    counts = 0
 
 
    For Each country In Sheet23.Range("A1:A52")
        start.Offset(0, counts) = "=RealGDPYoY!" & gdp.Offset(0, countd).Address(False, False) & ""
        Sheet23.Range(start.Offset(0, counts), start.Offset(1000, counts)).FillDown
        counts = counts + 1
        start.Offset(0, counts) = "=vlookup(RC[-1], RealGDPYoY!" & gdp.Offset(0, countd).Address(False) & ":RealGDPYoY!" & gdp.Offset(0, countd).Address(False) & ",3,false)"
        Sheet23.Range(start.Offset(0, counts + 1), start.Offset(1000, counts) + 1).FillDown
        counts = counts + 1
        start.Offset(0, counts) = "=vlookup(RC[-1],'Central Bank'!" & cb.Offset(0, countd).Address(False, False) & ":'Central Bank'!" & cb.Offset(1000, countd + 3).Address(False, False) & ",3,0)"
        Sheet23.Range(start.Offset(0, counts), start.Offset(1000, counts)).FillDown
        counts = counts + 1
        start.Offset(0, counts) = "=vlookup(RC[-1],'unemployment'!" & unemploy.Offset(0, countd).Address(False, False) & ":'unemployment'!" & unemploy.Offset(1000, countd + 3).Address(False, False) & ",3,0)"
        Sheet23.Range(start.Offset(0, counts), start.Offset(1000, counts)).FillDown
        counts = counts + 1
        start.Offset(0, counts) = "=vlookup(RC[-1],'cpi yoy'!" & cpi.Offset(0, countd).Address(False, False) & ":'cpi yoy'!" & cpi.Offset(1000, countd + 3).Address(False, False) & ",3,0)"
        Sheet23.Range(start.Offset(0, counts), start.Offset(1000, counts)).FillDown
        counts = counts + 1
        start.Offset(0, counts) = "=vlookup(RC[-1],'industrial production'!" & ip.Offset(0, countd).Address(False, False) & ":'industrial production'!" & ip.Offset(1000, countd + 3).Address(False, False) & ",3,0)"
        Sheet23.Range(start.Offset(0, counts), start.Offset(1000, counts)).FillDown
        counts = counts + 1
        countd = countd + 4
    Next country
End Sub

The problem occurs with this line

 start.Offset(0, counts) = "=vlookup(RC[-1], RealGDPYoY!" & gdp.Offset(0, countd).Address(False) & ":RealGDPYoY!" & gdp.Offset(0, countd).Address(False) & ",3,false)"

for some reason this string comes out as "=VLOOKUP(B11, RealGDPYoY!$I:$I:RealGDPYoY!$I:$I,3,FALSE)"

rather than "=vlookup(RC[-1], RealGDPYoY!$C9:RealGDPYoY!$C9,3,false)"

I can't figure this out. When I add a watch for the string, it comes out perfectly correct. Please help. This is excel 2010 on windows xp.

 

[jim may]

Shouldn't the Left-hand side of the assignment look like this?

start.Offset(0, counts).formulaR1C1 = "=vlookup(RC[-1], RealGDPYoY!" & gdp............."

 

[cbjohn1]

Not sure but it doesn't seem to affect anything

I think I figured out the issue though

=VLOOKUP(B11, RealGDPYoY!'B9':RealGDPYoY!'B9',4,FALSE)

what is causing 'B9' and what does that mean?

 

[cbjohn1]

excel version is 2010 and windows xp.