For/Next Loop not working

[kbates79]

I'm pretty new to VBA, but just when I thought I was getting the hang of it, this nested For/Next loop will not work the way I want it to.

Here is the code:
Dim I As Integer
Dim Count As Integer

For Count = 0 To 7
For I = 1 To 8
Range("B2").Select
ActiveCell.Offset(Count, 0).Value = WorksheetFunction. _
CountIf(Workbooks("EMEA.xlsx").Sheets(I).Columns(14), "REW")
Next I
Next Count

Basically I want it to loop through all the worksheets in a certain workbook, and then give me a total number for cells matching the "CountIf" criteria. The total shows up in a different workbook all together. Perhaps there is a VBA way of handling the count instead of using a worksheet function?

Thanks in advance for any help!

KB

 

[eyong123]

Actually I dont want it to repeat an entry for the same line number, but rather different entries by searchin the line and then checking if the offset (0,1) is empty and ready for new entry. Now if i enter two entry into the same line it erases the previous entry and enters the new one on top of the old entry since they have the same line number. So I want to enter data with same line number but different referencies and other information. Then it should stop the loop when the line number changes and indicate that there is no more blanc rows for that line number for new entry

I tried this but it didnt work

Private Sub CmdOK_Click()
Dim LijnFIND As Range

Application.ScreenUpdating = False
On Error Resume Next
With Sheets("Daily report Fab 1&2")
Set LijnFIND = .Range("B:B").Find(what:=cboLijn.Value, _
After:=.Range("B2"), _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Not LijnFIND Is Nothing Then
Do
If IsEmpty(LijnFIND.Offset(0, 1)) = False Then
LijnFIND.Offset(1, 0).Select
End If
Loop Until IsEmpty(LijnFIND.Offset(0, 1))

LijnFIND.Offset(, 1).Value = txtReferentie.Value
LijnFIND.Offset(, 2).Value = cboChocolateType.Value
LijnFIND.Offset(, 3).Value = cboUnscheduledHours.Value
If optROOD = True Then
LijnFIND.Offset(, 4).Value = cboNoofMixesPlanned.Value
LijnFIND.Offset(, 10).Value = cboNoofMixesProduced.Value
ElseIf optBLAUW = True Then
LijnFIND.Offset(, 5).Value = cboNoofMixesPlanned.Value
LijnFIND.Offset(, 11).Value = cboNoofMixesProduced.Value
ElseIf optGROEN = True Then
LijnFIND.Offset(, 6).Value = cboNoofMixesPlanned.Value
LijnFIND.Offset(, 12).Value = cboNoofMixesProduced.Value
ElseIf optGEEL = True Then
LijnFIND.Offset(, 7).Value = cboNoofMixesPlanned.Value
LijnFIND.Offset(, 13).Value = cboNoofMixesProduced.Value
ElseIf optORANJE = True Then
LijnFIND.Offset(, 8).Value = cboNoofMixesPlanned.Value
LijnFIND.Offset(, 14).Value = cboNoofMixesProduced.Value
Else
MsgBox "Problem with mixes, please check the data transferred"
End If

If cboDTR1.Value = "A10 (Insufficient Processing Knowledge)" Then
LijnFIND.Offset(0, 27) = txtDTR1.Value
ElseIf cboDTR1.Value = "A20 (Misunderstanding / unclear arrangements)" Then
LijnFIND.Offset(0, 28) = txtDTR1.Value
ElseIf cboDTR1.Value = "A30 (Labour Interruption)" Then
LijnFIND.Offset(0, 29) = txtDTR1.Value

End If

MsgBox "Transferred"

Else
MsgBox cboLijn.Value & " was not found. Please check your data."
.Activate
.Range("B3").Select
End If
End With

Application.ScreenUpdating = True
Unload Me
End Sub


Thanks once again for the enormous assistance

 

[eyong123]

ACtually i do not intend to add the samle value of the matching line. Each line more than one set of values. SO i want for each matching line to have more than 4 rows of the same line but different data set entered into at each entry


I look forward to your help

 

[jbeaucaire]

Your first sample code was easy to restructure without knowing what your variables did or what was going on in your data because it all was there to begin with, so I just had to move things around to the correct order.

The stuff you're talking about now I can't envension. Can you provide some sample data in BEFORE/AFTER layout perhaps I can extract the logic then.

Use Excel Jeanie to post up some visible examples of the data you're referring to, and perhaps a mockup of the desired results you have in mind.

 

[eyong123]

Dear Jerry, i have tried to install the Excel jeanie to no avail. Could I send you the sheet via email.

Actually I would like the function the way you did it, but I will like it ti find with 2 conditions:
1) the line number in the combo box
2) The offset (0,1) of the line number is empty

SO is it possible for it to be set to find these two conditions before performing task of data entry

Thanks

 

[jbeaucaire]

This verifies the offset, too:

If Not LijnFIND Is Nothing And Lijn.Offset(,1) = "" Then

But what if it's not?

 

[eyong123]

thanks Jerry I will try this. But if its not then it should take the next available Select the next row that has the available cell and the same line, and if the option is not available because line number changes then it should indicate with a message that no more entry can be made for that line number

Once again appreciation

 

[eyong123]

HI Jerry I hav tried the code and it still does the same, replacing the initial entry

 

[jbeaucaire]

Typo, maybe this:

If Not LijnFIND Is Nothing And LijnFIND.Offset(,1) = "" Then

 

[eyong123]

Hi Jerry, I want to thank you very sincerely for ur assistance without which I could not have done anything on my code.

I wish to ask how I can run the same procedure for a combo box through a number of combo box. The combo boxes are labeled cboDTR1, cboDTR2 up to 6. For now I have copied the codes and repeat it to the other combo boxes and then change the numbers. this obviously makes my procedure too large.

So is there a way of running the same procedure through different combo boxes

NB: The repetition of the procedure comes after the set find function

thanks

 

[jbeaucaire]

Post up examples of two sets of combobox reference code so i can see what is same/different in each set.