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 :confused: 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.

:wave: