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

[Ecniv]

If they are fairly small, put them both in a zip file and post the file as an attachment to your next post. If they are some what large, perhaps cut it down to small and post.

Let me put in writing what I think is meant to happen on the sheet.

You open a source file (one sheet called QCTotals)
You open the Off Master Workbook (contains 6 sheets)
You loop 6 times, and for each loop you put the data into a different sheet.
You create arrays and fill them
- column 2 and 3 hold something you are matching on
- column 1 and 2 hold the variables to write to the new sheet
You loop through the array and compare column 3 with column 2
- if there is a match you put the data into the source sheet at the same row as in the target sheet you are currently reading
Keep looping until all 6 sheets have been checked, then close off everything.

Now if that is what is what you wanted, you are pretty close.
However I've put a bit in italics as I think this may cause you a problem. If you want the matches from any of the target sheets put into the source sheet, you need to have source row pointer to know where you are.

Post up if you think this is what you want and I'll explain it. Or if I've got it all wrong, please let me know

[elocin_rae]

Thank you so much for being patient with me...

heres what I'm trying to do..

You open a source file (one sheet called QCTotals)
You open the Off Master Workbook (contains 6 sheets)
You loop 6 times, and for each loop you take data from the off master workbook and match if applicable match it with the data in the source file.
You create arrays and fill them
- column 2 hold something you are matching on
- column 1 and 2 hold the variables to write to the sorce file
- if there is a match you put the data into the source sheet row 1 and 3 where column 3 from off master matches column 2 of the souce.
Keep looping until all 6 sheets have been checked, then close off everything.

I feel like I'm getting more confusing

[Ecniv]

Not a problem.

Looks like what you wrote is what I said, so I am getting what you are trying to do.


I am just looking into the sheets now, do you not use Option Explicit?
hmmm tricky.

Let me do some stuff as an example and post back in a while, ok?

[Ecniv]

Ok,

Attached is a spreadsheet (zipped).
Open the zip and copy the spreadsheet out.
Open the spreadsheet (see that it is blank - only has code).
As a test run - open the vb editor, then ctrl+g (immediates window) and type in this:
QCCheck "<path to QC Totals file>","<path to off masters file">,true,true
Press return and it should run (hopefully) with no errors.

You are welcome to look through the code and grab the parts you want, or use the whole as it is, but please, try to understand how I did it if you do use it, as if there is an error you will have to fix it.

[elocin_rae]

Yeah, it works, thank you so very much, your awesome.

I changed a few things so I could understand it better, and I will use the code you gave me but before I can do that with confidence. I want to understand what everything does. I've only got a couple things left, see the code below, correct me where I'm wrong in my comments within the code.

  For lngRowQC = 3 To lngMaxQC 'lngrowqc is row 3 to last entity listed in column 2 of the qctotal worksheet
            blnMatched = False   'I don't know what this is
            strQCStr = wshtSource.Cells(lngRowQC, 2)   'this = what the entities in column 2 are.
            
            For lngMasterSht = 1 To 6   'this says there are 6 worksheets

the code below I just need help understanding better, as far as definitions. I do understand how this works just need to understand why. I think thats what I mean.

                   If lngMaxMaster > 3 Then
                        For lngRowMaster = 3 To lngMaxMaster
                            If strQCStr = wshtTarget.Cells(lngRowMaster, 3) Then
                                blnMatched = True
                                wshtSource.Cells(lngRowQC, 1) = wshtTarget.Cells(lngRowMaster, 2)
                                wshtSource.Cells(lngRowQC, 3) = wshtTarget.Cells(lngRowMaster, 1)
                            End If
                            If blnMatched Then Exit For
                        Next
                    End If
                
                Else
'---- using .find
                    Set rng = wshtTarget.Range(wshtTarget.Cells(3, 3), wshtTarget.Cells(lngMaxMaster, 3))
                
                    If Not rng.Find(strQCStr) Is Nothing Then
                        lngRowMaster = rng.Find(strQCStr).Row
                        blnMatched = True
                        wshtSource.Cells(lngRowQC, 1) = wshtTarget.Cells(lngRowMaster, 2)
                        wshtSource.Cells(lngRowQC, 3) = wshtTarget.Cells(lngRowMaster, 1)
                    End If
                End If

                If blnMatched Then Exit For
            Next
        
        Next
        
        If blnCloseMaster Then wbTarget.Close False
    End If
    
    If Err.Number <> 0 Then
        MsgBox "Error : " & Err.Number & vbCrLf & Err.Description, vbOKOnly + vbInformation, "Error"
    Else

Thank you again
elocin

[Ecniv]

I changed a few things so I could understand it better, and I will use the code you gave me but before I can do that with confidence. I want to understand what everything does. I've only got a couple things left, see the code below, correct me where I'm wrong in my comments within the code.

  For lngRowQC = 3 To lngMaxQC 'lngrowqc is row 3 to last entity listed in column 2 of the qctotal worksheet
            blnMatched = False   'I don't know what this is
            strQCStr = wshtSource.Cells(lngRowQC, 2)   'this = what the entities in column 2 are.
            
            For lngMasterSht = 1 To 6   'this says there are 6 worksheets

Yup loops from row 3 to what ever the lowest row is
blnMatched is a boolean (True/False) variable. The idea is that if it matches you only want that one match, so it stops the loops (thus runs faster). I have to reset it each time you check from the main set of variables to the 6 sheets.

the code below I just need help understanding better, as far as definitions. I do understand how this works just need to understand why. I think thats what I mean.

                   If lngMaxMaster > 3 Then
                        For lngRowMaster = 3 To lngMaxMaster
                            If strQCStr = wshtTarget.Cells(lngRowMaster, 3) Then
                                blnMatched = True
                                wshtSource.Cells(lngRowQC, 1) = wshtTarget.Cells(lngRowMaster, 2)
                                wshtSource.Cells(lngRowQC, 3) = wshtTarget.Cells(lngRowMaster, 1)
                            End If
                            If blnMatched Then Exit For
                        Next
                    End If
                
                Else
'---- using .find
                    Set rng = wshtTarget.Range(wshtTarget.Cells(3, 3), wshtTarget.Cells(lngMaxMaster, 3))
                
                    If Not rng.Find(strQCStr) Is Nothing Then
                        lngRowMaster = rng.Find(strQCStr).Row
                        blnMatched = True
                        wshtSource.Cells(lngRowQC, 1) = wshtTarget.Cells(lngRowMaster, 2)
                        wshtSource.Cells(lngRowQC, 3) = wshtTarget.Cells(lngRowMaster, 1)
                    End If
                End If

                If blnMatched Then Exit For
            Next
        
        Next
        
        If blnCloseMaster Then wbTarget.Close False
    End If
    
    If Err.Number <> 0 Then
        MsgBox "Error : " & Err.Number & vbCrLf & Err.Description, vbOKOnly + vbInformation, "Error"
    Else

Thank you again
elocin

Right, this is a little tricky.

The top part of the If statement does what yours initially did, it loops through the sheets. To try and save getting the same variable all the time, I held the QC Totals string before entering the loop. Then it goes through each of the 6 sheets comparing the third column with the string variable held.

If it matches, then I send the rows data to the correct columns in the QC Totals sheet, and set the blnMatched to say I have a match (so then exits the 6 sheets loop) and to move on to the next row in the QC Totals sheet.

The second half of the if statement is to use Excels inbuilt (and slightly faster) search function. To restrict it I set a range of the column first. Then using .Find it returns either a cell or nothing, so I check whether it is not nothing. If it is not nothing then there is a match, and I do like I did before (paragraph up).

Hope that helps.

[elocin_rae]

thank you again for all your help.