Loop through excel rows and call VBA functions

Question

I need to be able to loop through my rows (specifically, column B), and use the number in a certain cell in order to do specific functions using other cells in that row. For example, Rule #1 indicates that I need to find last modified date of the path in the cell next to the Rule #, but the task is different for each Rule.

I'm new to VBA and I've just been struggling with setting up a loop and passing variables to different subs, and would hugely appreciate any help. To be clear, I'm looking for syntax help with the loop and passing variables

Thank you!

Reference Images: The spreadsheet

The attempt at sketching out the code

Private Sub CommandButton1_Click()
    Dim x As Integer
    NumRows = Range("B2", Range("B2").End(xlDown)).Rows.Count
    Range("B2").Select
    For x = 1 To NumRows
        If Range(RowCount, 1).Value = 1 Then
             RuleOne (RowCount)
        End If
    Next

    'Dim RowCount As Integer
    'RowCount = 1
    'Worksheets("Sheet2").Cells(1, 2) = Worksheets("Sheet1").UsedRange.Row.Count

    'While RowCount < Worksheets("Sheet1").Rows
      'If Worksheets("Sheet1").Cells(RowCount, 1).Value = 1 Then
          'RuleOne (RowCount)
      'End If
    'Wend
End Sub

Sub RuleOne(i As Integer)
    'use filedatetime and path from i cell
    'Worksheets("Sheet2").Cells(1, 1) = FileDateTime(C, i)
    Worksheets("Sheet2").Cells(1, 1) = "hello"
End Sub

Sub RuleTwo(i As Integer)
    Worksheets("Sheet2").Cells(1, 1) = "hello"
End Sub

Please edit your question to include code in the body of question and not as a snapshot. — skkakkar, Jul 20 '16 at 13:26
Are you getting errors? If so, try taking the parenthesis away from the variables you pass to the function. `RuleOne RowCount` — BruceWayne, Jul 20 '16 at 13:44
http://stackoverflow.com/questions/22645347/loop-through-all-subfolders-using-vba — Nathan_Sav, Jul 20 '16 at 13:45
@Nathan_Sav that question is quite different in that it is trying to loop through subfolders in a folder, but I really appreciate it. — wiesliam, Jul 20 '16 at 13:52
@BruceWayne the first error I'm getting is presumably very simple, it's with the line: If Range(RowCount, 1).Value = 1 Then — wiesliam, Jul 20 '16 at 13:54
Sorry, I copied the wrong link, but from the File System Object you can get the created and last modified dates. — Nathan_Sav, Jul 20 '16 at 14:00
You never set `RowCount` as a variable. That's why you're getting an error. Do you mean `NumRows`?? Also, I'd add `Option Explicit` above `Private Sub ...` to force declaration of all variables, this could have helped catch `RowCount` wasn't declared. — BruceWayne, Jul 20 '16 at 14:22

score 0 · Answer 1 · answered Jul 20 '16 at 13:58

0

Try to change the Range(RowCount, 1).Value = 1 to Cells(x, 2).Value = 1.

answered Jul 20 '16 at 13:58

Anastasiya-Romanova 秀

3,326
28
48

score 0 · Answer 2 · answered Jul 20 '16 at 16:08

The variable RowCount has not been initialised/set. I assume this is what this variable is meant to be the number in column B

RowCount = Cells(x, "B").Value

I also noticed that the variable NumRows seemed to be one less than it should be (so if the last row was 1 it would skip it). So I used this instead:

NumRows = Cells(Rows.Count, "B").End(xlUp).Row

So try this code:

Sub CommandButton1_Click()
    Dim x As Integer
    NumRows = Cells(Rows.Count, "B").End(xlUp).Row
    For x = 1 To NumRows
        RowCount = Range("B" & x).Value
        If RowCount = 1 Then
             RuleOne (x)
        End If
    Next

    'Dim RowCount As Integer
    'RowCount = 1
    'Worksheets("Sheet2").Cells(1, 2) = Worksheets("Sheet1").UsedRange.Row.Count

    'While RowCount < Worksheets("Sheet1").Rows
      'If Worksheets("Sheet1").Cells(RowCount, 1).Value = 1 Then
          'RuleOne (RowCount)
      'End If
    'Wend
End Sub

Sub RuleOne(i As Integer)
    'use filedatetime and path from i cell
    'Worksheets("Sheet2").Cells(1, 1) = FileDateTime(C, i)
    Worksheets("Sheet2").Cells(1, i) = "hello"
End Sub

Sub RuleTwo(i As Integer)
    Worksheets("Sheet2").Cells(1, 1) = "hello"
End Sub

Loop through excel rows and call VBA functions

2 Answers2