Application-defined or object defined error in Excel 2010 VBA

[hsejar]

Hi,
Iam really new to VB programming in excel and i have to check an error which im getting in an existing piece of code. I am getting an "Runtime error 1004 Application-defined or object defined error" on the highlighted line when I run this sub....can someone please help.

i'm getting wan error in the below colored loop beginner. i.e (For Each varCCEntry In Workbooks(strWorkbook).Worksheets("MD_LogisticStructure").Range(strRegion)


Code:
Sub PrintLogisticStructure(ByVal strWorkbook As String, ByVal strRegion As String, ByVal lngRowCounter As Long, ByVal strSheet As String, ByVal strCurrency As String, ByVal lngDateIndex As Long, ByVal dblPER As Double)
Dim RegionSum(1 To 25) As Double
Dim varCCEntry As Variant
Dim strCCEntry As String
Dim varPlantEntry As Variant
Dim lngCounter&
Dim strKey2$
Dim strKey3$
Dim rngMD As Range
Dim rngMDCC As Range

'initialize the array which is used to sum
'up the required values
For lngCounter = LBound(RegionSum) To UBound(RegionSum)
RegionSum(lngCounter) = 0
Next lngCounter

lngCounter = 0

'get the keys to find it in the query
strKey2 = Workbooks(strWorkbook).Worksheets("Data").Cells(lngRowCounter, 2).Value
strKey3 = Workbooks(strWorkbook).Worksheets("Data").Cells(lngRowCounter, 3).Value

If Left$(strKey3, 3) = "Tar" Then
'set targets to ""
For lngCounter = 13 To 25
Workbooks(strWorkbook).Worksheets("Data").Cells(lngRowCounter, lngCounter - 5).Value = ""
Next lngCounter

For lngCounter = 1 To 13
Workbooks(strWorkbook).Worksheets("Data").Cells(lngRowCounter + 1, lngCounter + 7).Value = ""
Next lngCounter

Exit Sub
End If


'for each entry which belongs to the region
For Each varCCEntry In Workbooks(strWorkbook).Worksheets("MD_LogisticStructure").Range(strRegion)


'check if it is a subregion or really a companycode
If IsNumeric(varCCEntry) And varCCEntry <> "" Then

'if it is a companycode, convert the variant to a string
strCCEntry = varCCEntry

'check if the CompanyCode is in the MD_CompanyCode-sheet
Set rngMD = Workbooks(strWorkbook).Worksheets("Master_Data").Columns(3).Find(strCCEntry, LookIn:=xlValues, lookat:=xlWhole)
If Not rngMD Is Nothing Then
strccnrdes = Workbooks(strWorkbook).Worksheets("Master_Data").Cells(rngMD.Row, 1).Value
End If

Set rngMDCC = Workbooks(strWorkbook).Worksheets("MD_CompanyCode").Columns(1).Find(strccnrdes, LookIn:=xlValues, lookat:=xlWhole)
If Not rngMDCC Is Nothing And Not rngMD Is Nothing Then

'for each plant which belongs to the companycode
For Each varPlantEntry In Workbooks(strWorkbook).Worksheets("MD_CompanyCode").Range("CCode" & strCCEntry)

'write the plant next to the companycode in the chart-sheet
Workbooks(strWorkbook).Worksheets("Chart").Cells(7 + lngCounter, 32).Value = varPlantEntry
lngCounter = lngCounter + 1

'get the plantnumber as the first four ciphers
strPlantNumber = Left(varPlantEntry, 4)

'add the plant-values to the actual sum
Call AddPlant(strWorkbook, RegionSum, strPlantNumber, strKey2, strKey3, strSheet, strCurrency, lngDateIndex, dblPER)
Next varPlantEntry
End If
End If
Next varCCEntry

'sort the plantlist
Call SortDisplayedPlants(strWorkbook, lngCounter)

'print the actual-year values
For lngCounter = 13 To 25
Workbooks(strWorkbook).Worksheets("Data").Cells(lngRowCounter, lngCounter - 5).Value = RegionSum(lngCounter)
Next lngCounter

'print the pre-year values
For lngCounter = 1 To 13
Workbooks(strWorkbook).Worksheets("Data").Cells(lngRowCounter + 1, lngCounter + 7).Value = RegionSum(lngCounter)
Next lngCounter

End Sub

<STYLE>.alt2 font {font: 11px monospace !important;color: #333 !important;}</STYLE>

 

[mirabeau]

does it work if you change

Workbooks(strWorkbook).Worksheets("MD_LogisticStructure").Range(strRegion)

to

Workbooks(strWorkbook).Worksheets("MD_LogisticStructure").Range("strRegion")

?

 

[hsejar]

no...it doesnt ....
It is a string passed on to the sub, this string has a value "LESW" when this sub is called. that string is available in the worksheet "MD_LogisticStructure" in the cell A44. this worksheet MD_LogisticStructure contains a list of regions in the cell A1 to A64.
<!-- / message -->