Run time error ‘1004’

Hi there was wondering if anybody could help I have this code which I am trying to set up so as depending on the month the rest of the code will use a different sheet. My sheets are called TPM1, TPM2 and so on up to TPM 12. the code below for instance works fine but It is fixed on TPM10.

dKArray(y, x) = Range("TPM10!" + sCol + CStr(y + 9)).Value

therefore I changed this to read.

[code]iMonth = Worksheets("Clear").Range("E4").Value[code]

dKArray(y, x) = Range("'TPM" + CStr(iMonth) + sCol + CStr(y + 9)).Value

where iMonth is the number of month located in cell E4 and set as integer but for this I get a

Run time error ‘1004’: Method “Range of object_Global failed error

Can anybody help me/ tell me how to do what I want to. I am new to this and Visual basic for dummies isn’t much use.

Cheers David

Hi David,

Your code is missing the closing ' around the sheet name and an exclamation mark:

dKArray(y, x) = Range("'TPM" & CStr(iMonth) + "'!" + sCol + CStr(y + 9)).Value

In VBA, it is better to use & to concatenate strings rather than +.

Also, rather than using syntax like this:

Range("SomeWorksheet!RangeAddress").Value

It is better to use this:

Worksheets("SomeWorksheet").Range("RangeAddress").Value

So, taking into account the above suggestions, your code would then become:

dKArray(y, x) = Worksheets("TPM" & CStr(iMonth)).Range(sCol & CStr(y + 9)).Value