Invalid Procedure Error 5 when assigning value to variable

[SirProperJob]

Hi,

I'm having problems with an error showing when I try to assign a value to a string variable, saying that's an invalid procedure, when I haven't even called one! It works ok if i step through the code, but if i try to run the whole lot, the error keeps coming up. My code is as below:

Sub DanoneExtract()
Dim wbOpen, wbTemp As Workbook
Dim iRow, iUnits, iSheets, x, c, ws As Integer
Dim strExtension, strCry, strCat, strBO, strBrand, strPL, strPriceSeg, strCal, strCarb, strWS, strFl, strPackMat, strPackType, strPackSize, strGeoScope, strChan As String
Dim dblVolShare, dblTotVol, dblVol, dblVal As Double
Const strPath As String = "G:\EDIT\REPORTS\SINGLE CLIENT REPORTS\Danone\2013\00_Project Management\Value Methodology\Suzanna\Dummy Data_Template Test\"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ChDir strPath
strExtension = Dir(strPath & "*.xls*")
Set wbTemp = Workbooks.Open("G:\EDIT\REPORTS\SINGLE CLIENT REPORTS\Danone\2013\00_Project Management\Value Methodology\Suzanna\DanoneUploadTemplate.xlsx")
Do While strExtension <> ""
        Set wbOpen = Workbooks.Open(strPath & strExtension)
        wbOpen.Worksheets(1).Activate
        strCry = Right(Range("A1").Value, Len(Range("A1").Value) - 28).Value
        iRow = 2
        iSheets = wbOpen.Worksheets.Count

The error occurs when I try to assign the value Right(Range("A1").Value, Len(Range("A1").Value) - 28).Value to strCry.

Any ideas as to why this might be happening? Later on in the code i have assigned similar values to other variables with no problems.

 

[wigi]

You are not correct on your declarations. this code:

Dim strExtension, strCry, strCat, strBO, strBrand, strPL, strPriceSeg, strCal, strCarb, strWS, strFl, strPackMat, strPackType, strPackSize, strGeoScope, strChan As String

set strChan as a String. The other variables are Variant's (no type declaration). You need to repeat the "As String" part for each String variable. Likewise for Long, Integer, ...

 

[SirProperJob]

Thanks for replying and I tried setting all my variables as you suggested, but i still get the same error. It's really frustrating, because it works fine when i step through each line, but when i try to run the whole thing it comes up with this error!

 

[Firefly2012]

What value is in A1?

 

[SirProperJob]

It has the text "PRICE COLLECTION TEMPLATE - ARGENTINA". I can't change this, as much as i'd love to, as my "superiors" have sigend off on this template. Obviously, "ARGENTINA" changes for each file.

 

[Firefly2012]

Silly me, you have a superfluous .Value on the end - your line should be:

strCry = Right(Range("A1").Value, Len(Range("A1").Value) - 28)

 

[SirProperJob]

Still no joy. I've tried changing the name of the variable as well and it still kicks up a fuss.

 

[Norie]

What is it you are trying to extract from A1?

If it's what's after PRICE COLLECTION TEMPLATE - .

strCry = Trim(Mid(Range("A1").Value, 28))

 

[SirProperJob]

Perfect! I always forget about Mid when i'm using VBA, and it seems to have done the trick here. Thanks a lot!