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

 

[Norie]

I don't think the CountIf is the problem.

It's probably because you aren't using any worksheet or workbook references anywhere else in the code.

So you get the right result but it's put in the wrong worksheet/workbook.

Where do you want the results to go?

You could try this but it's basically a shot in the dark.

It puts the results in column B of the active sheet of the workbook the code is in.

Option Explicit
Sub AllTheCounts()

Dim I As Long
Dim rw As Long
 
    For rw = 0 To 7
        For I = 1 To 8
            ThisWorkbook.ActiveSheet.Cells(rw + 2, 2).Value = WorksheetFunction. _
                                                              CountIf(Workbooks("EMEA.xlsx").Sheets(I).Columns(14), "REW")
        Next I
    Next Count
 
End Sub

 

[jbeaucaire]

How about:

Dim I As Long

For I = 1 To 8
    Range("B1").Offset(I).Value = Application.WorksheetFunction. _
        CountIf(Workbooks("EMEA.xlsx").Sheets(I).Columns(14), "REW")
Next I

 

[kbates79]

Using one single "For" statements works, thanks!

To clarify: what I want it to do is look in a particular column across all the sheets in workbook "EMEA.xlsx", count the instances of "REW", and return the count to a cell in a different workbook. This bit of code is part of a much longer procedure, and by the time it gets to this point, the active workbook is the one that I want the data in.

I'm still wondering why the nested "For" won't work?... But the single "For" statement works like a charm

Thanks!
~KB

 

[Norie]

The nested loop should work and the reason your code didn't work is probably because of what I mentioned previously.

This sort of thing, eg results going to wrong places, tends to happen if you don't reference things properly, especially if you are working with multiple sheet/files.

 

[eyong123]

Guys i am in desperate need of ur help. i am very new to VBA. I am trying a double loop using a dialog box to enter data into a spread sheet as follow.
The Code selects the cell in column B and first loops until it finds a specific value that matches the entry in a dialog box(lijn No.). then it selects there ajacent cell (activecell.offset(0,1)(column C). The input in the dialog box(lijn No.) will occur more than twice. SO therefore I want the second loop to go down the ajacent cell(column C) untill the lijn No in column B changes say from L01 to L02.


Here is my code so far
rivate Sub CmdOK_Click()


ActiveWorkbook.Sheets("Daily report Fab 1&2").Activate
Range("B3").Select
Do
If ActiveCell.Value = cboLijn.Value Then
If IsEmpty(activececell) = False Then
ActiveCell.Offset(1, 0).Select
End If
End If
Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Offset(0, 1).Select
ActiveCell.Value = txtReferentie.Value
ActiveCell.Offset(0, 1) = cboChocolateType.Value
ActiveCell.Offset(0, 2) = cboUnscheduledHours.Value
If optROOD = True Then
ActiveCell.Offset(0, 3) = cboNoofMixesPlanned.Value
ElseIf optBLAUW = True Then
ActiveCell.Offset(0, 4) = cboNoofMixesPlanned.Value
ElseIf optGROEN = True Then
ActiveCell.Offset(0, 5) = cboNoofMixesPlanned.Value
ElseIf optGEEL = True Then
ActiveCell.Offset(0, 6) = cboNoofMixesPlanned.Value
ElseIf optORANJE = True Then
ActiveCell.Offset(0, 7) = cboNoofMixesPlanned.Value
End If

If optROOD = True Then
ActiveCell.Offset(0, 9) = cboNoofMixesProduced.Value
ElseIf optBLAUW = True Then
ActiveCell.Offset(0, 10) = cboNoofMixesProduced.Value
ElseIf optGROEN = True Then
ActiveCell.Offset(0, 11) = cboNoofMixesProduced.Value
ElseIf optGEEL = True Then
ActiveCell.Offset(0, 12) = cboNoofMixesProduced.Value
ElseIf optORANJE = True Then
ActiveCell.Offset(0, 13) = cboNoofMixesProduced.Value
End If



Range("C3").Select


End Sub


Private Sub cmdUndoEntry_Click()
Call UserForm_Initialize
End Sub


Please I need you help. this has been freezing my computer everytime I run it

 

[jbeaucaire]

As you move into VBA you have to stop thinking of sheets in terms like humans, activating and selecting sheets and cells before you can act on them. VBA does not have that restriction. AS well as it is very inefficient.

Instead of looping through cells one at a time looking for a single value, you can jump directly to it using a find command, then put your data into that found row.

Option Explicit

Private Sub CmdOK_Click()
Dim LijnFIND As Range

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
        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
        
        MsgBox "Transferred"
        
    Else
        MsgBox cboLijn.Value & " was not found. Please check your data."
        .Activate
        .Range("B3").Select
    End If
End With

End Sub

 

[eyong123]

Perfect.
Thanks dear..its awesome.

 

[eyong123]

Hi Jerry
I did that and it worked very well. Now i have another issue.
My column is arranged like this
Lijn Referentie
L01
L01
L01
L01

L02
L02
L02

so after filing a row in L01, how can i have a second loop that willb fill again on L01 by checking the next available L01 with a empty Referentie..


I look forward to ur help

cheers

 

[jbeaucaire]

If I added a loop to that code, I would expect to add the same values to all matching rows. Is that what you mean?