Some cells are populating incorrectly [resolved]

[elocin_rae]

Hello, I need a little help.

In my source workbook I'm trying to generate rows 1(var1) and 3 (var2). Everything seems to populates correctly but at a closer glance, Var1 gets populated with a 1 and Var3 gets nothing. I have checked the target workbook to make sure that the information for Var1 and Var2 are present, and everything is there and should be populating.

Can anyone please help me? Thanks

Public Sub Name_Match_Click()

   'get the current workbook and sheet
    Set wbSource = ActiveWorkbook
    Set wshtSource = wbSource.Worksheets("QCTotals")
    
     'open a external workbook
    Set wbTarget = Workbooks.Open("C:\WINNT\Profiles\nmayer\Desktop\learn excel\IITC Offload Master List.xls")

 For z = 1 To 6

 If z = 1 Then
    Set wshtTarget = wbTarget.Worksheets("SITCO Archive")
    ElseIf z = 2 Then
     Set wshtTarget = wbTarget.Worksheets("LTI Archive")
    ElseIf z = 3 Then
    Set wshtTarget = wbTarget.Worksheets("THTI Archive")
    ElseIf z = 4 Then
    Set wshtTarget = wbTarget.Worksheets("CNI Archive")
    ElseIf z = 5 Then
    Set wshtTarget = wbTarget.Worksheets("HHT Archive")
    ElseIf z = 6 Then
    Set wshtTarget = wbTarget.Worksheets("Offload Master")
 End If
    'get the required data and do some tricky maths with it
   Rowcount = wshtTarget.UsedRange.Rows.Count

'defining size of array
ReDim DName(3 To Rowcount)
ReDim Var1(3 To Rowcount)
ReDim Var2(3 To Rowcount)
ReDim SList(3 To Rowcount)


'_Builds the arrays that contains all Drawing Information
For x = 3 To Rowcount



    DName(x) = wshtTarget.Cells(x, 3).Value
    Var1(x) = wshtTarget.Cells(x, 1).Value
    Var2(x) = wshtTarget.Cells(x, 2).Value
    SList(x) = wshtSource.Cells(x, 2).Value

Next

'set iitcqa as active workbook here


For x = 3 To Rowcount

    For y = 3 To Rowcount
   
        If UCase(DName(x)) = UCase(SList(y)) Then
            wshtSource.Cells(y, 3).Value = Var1(x)
            wshtSource.Cells(y, 1).Value = Var2(x)
            y = Rowcount
        End If
    Next
  
Next

   'close the external workbook and clear up
   If z = 6 Then
    wbTarget.Close
        Set wshtSource = Nothing
        Set wbSource = Nothing
        Set wshtTarget = Nothing
        Set wbTarget = Nothing
    End If
 Next
End Sub

[Ecniv]

Hi, can I suggest that the following

If z = 1 Then
    Set wshtTarget = wbTarget.Worksheets("SITCO Archive")
    ElseIf z = 2 Then
     Set wshtTarget = wbTarget.Worksheets("LTI Archive")
    ElseIf z = 3 Then
    Set wshtTarget = wbTarget.Worksheets("THTI Archive")
    ElseIf z = 4 Then
    Set wshtTarget = wbTarget.Worksheets("CNI Archive")
    ElseIf z = 5 Then
    Set wshtTarget = wbTarget.Worksheets("HHT Archive")
    ElseIf z = 6 Then
    Set wshtTarget = wbTarget.Worksheets("Offload Master")
 End If

be changed to:

Select case z
    case 1
       strTargetSheet = "SITCO Archive"
    case 2
       strTargetSheet = "LTI Archive"
    case 3
       strTargetSheet = "THTI Archive"
    case 4
       strTargetSheet = "CNI Archive"
    case 5
       strTargetSheet = "HHT Archive"
    case 6
       strTargetSheet = "Offload Master"
 End case
 Set wshtTarget = wbTarget.Worksheets(strTargetSheet)

where strTargetSheet is a string dimmed at the top of the sub
Makes it easier to read??

Um, your sode suggests that you are reading vertically from the source...
And when it writes its row, column ... so you are filling rows in column 1 and column 3 (correct?).

Where does the values not work right?
Are you sure that the data put into the initial array is correct?
Are you doing what could be done with a Vlookup function (in excel) or sum if (also in excel) ??

Can you post example data (soruce and required result) ?

[elocin_rae]

I can't post any examples but after spending more and more time looking at it, I found that my problem might be with the rowcount. Its not doing a full rowcount.

Public Sub Name_Match_Click()

   'get the current workbook and sheet
    Set wbSource = ActiveWorkbook
    Set wshtSource = wbSource.Worksheets("QCTotals")
    
     'open a external workbook
    Set wbTarget = Workbooks.Open("C:\WINNT\Profiles\nmayer\Desktop\learn excel\Off Master.xls")

 For Z = 1 To 6

 If Z = 1 Then
    Set wshtTarget = wbTarget.Worksheets("SIMPSON")
    ElseIf Z = 2 Then
     Set wshtTarget = wbTarget.Worksheets("LITTLE")
    ElseIf Z = 3 Then
    Set wshtTarget = wbTarget.Worksheets("THEATRE")
    ElseIf Z = 4 Then
    Set wshtTarget = wbTarget.Worksheets("MARGE")
    ElseIf Z = 5 Then
    Set wshtTarget = wbTarget.Worksheets("HOMER")
    ElseIf Z = 6 Then
    Set wshtTarget = wbTarget.Worksheets("OFFICE")
 End If
    'get the required data and do some tricky maths with it
   Rowcount = wshtTarget.UsedRange.Rows.Count
'   Rowamount = wshtSource.UsedRange.Rows.Count
     
   
'defining size of array
ReDim SList(3 To Rowcount)  'internal names from namematcher_find path.xls
ReDim DName(3 To Rowcount) 'internal names from Off Master.xls
ReDim Var1(3 To Rowcount)   'partner names from Off Master.xls
ReDim Var2(3 To Rowcount)   'project names from Off Master.xls




'_Builds the arrays that contains all Drawing Information
For X = 3 To Rowcount
'For W = 3 To Rowamount



    SList(X) = wshtSource.Cells(X, 2).Value
    DName(X) = wshtTarget.Cells(X, 3).Value
    Var1(X) = wshtTarget.Cells(X, 1).Value
    Var2(X) = wshtTarget.Cells(X, 2).Value
   

Next

'set iitcqa as active workbook here


For X = 3 To Rowcount

    For y = 3 To Rowcount
   
        If UCase(DName(X)) = UCase(SList(y)) Then
            wshtSource.Cells(y, 3).Value = Var1(X)
            wshtSource.Cells(y, 1).Value = Var2(X)
            y = Rowcount
        End If
    Next
  
Next

   'close the external workbook and clear up
   If Z = 6 Then
    wbTarget.Close
        Set wshtSource = Nothing
        Set wbSource = Nothing
        Set wshtTarget = Nothing
        Set wbTarget = Nothing
    End If
 Next
End Sub

I have some files that I can give you as an example, but I'm unsure how to get them to you. let me know.

Thanks
elocin

[Ecniv]

Hi,

If the rowcount is causing a problem, try this line:

rowcount=wshtTarget.usedrange.row

This returns the bottom row of the used range in the sheet.
It may not be accurate though, as the used range could be with no data at the bottom (something to keep in mind).

The alternative (if you know that column 1 has data) is to move from the bottom up.

rowcount = wshtTarget.cells(65530,1).End(xlUp).row

Then check that rowcount is greater than your title row (three wasn't it?).
If it is less than or equal to 3 then there is no data...

Hope that helps, post up if it works (or not)

[elocin_rae]

Thanks for the try but it didn't work. I get a run-time error 9. Subscript out of range.

if you want I have 2 test files that I can give you to look at, just tell me how I can give them to you.

Thanks again.

elocin

[elocin_rae]

Hi again,

Looking into it more, I'm starting to think that my code is only getting information from one of the six sheets in the off master.xls I really want to understand whats going wrong, but nothing seems to want to work.

Do I need a sepearte rowcount for my slist? it is a diffrent xls file.

Elocin