CODEWITHSUNDEEP
excelvbaloopsif-statementrange
Uru
Uru

Reputation: 25

How to write two IF statements for different ranges in a loop, VBA

I am working on an Excel document using VBA. This document contains a database with multiple columns, but for simplicity, let's say I have 2 columns:

I'm trying to create a macro that checks all the numbers in column F (with a loop). If the number is above 100, then check the adjacent cell in column C. If the name corresponds to a condition (let's say corresponds to John or Tom), then add the value of the number in another sheet. If none of those apply, check the next cell.

My problem is that I can't find a way to define the cells in column C (Creating a variable/object to call the cells or calling directly the adjacent cell).

My code looks like this:

Sub Test1()

    Dim rngnumbers, rngnames, MultipleRange As Range

    Set rngnumbers = Sheet2.Range("F2:F999")    
    Set rngnames = Sheet2.Range("C2:C999")
    Set MultipleRange = Union(rngnumbers, rngnames)

        For Each numb In rngnumbers
            If numb.Value >= 100 Then
                    If Sheet2.Range("C2") = "John" Or Sheet2.Range("C2") = "Tom" Then '''The problem here is that it only looks at the cell C2 and not the adjacent cell
                        Sheet1.Range("I999").End(xlUp).Offset(1, 0).Value = numb.Value
                    Else
                    End If
            End If
        Next numb

End Sub

I tried modifying the line:

'If Sheet2.Range("C2") = "John" Or Sheet2.Range("C2") = "Tom" Then' to something like: 'newname.String = "John" '

But I can't find a way to define newname. Another idea would be to increment the If statement for the names within the For loop.

Additional note: I am also not using formulas directly within Excel as I don't want any blank cells or zeros when the if functions are False.

Upvotes: 2

Views: 1847

Answers (4)

wallyeye
wallyeye

Reputation: 116

I normally work with arrays:

Sub Test1()

Dim rngnumbers    As Excel.Range

Dim arrVals       As variant 

Dim lngRow        As long

Arrvals = Sheet2.Range("C2:F999").value

    For Lngrow = lbound(arrvals,1) to ubound(arrvals,1)
        If arrvals(lngrow,4) >= 100 Then
                If arrvals(lngrow,1)= "John" Or arrvals(lngrow,1) = "Tom" Then '''The problem here is that it only looks at the cell C2 and not the adjacent cell
                    Sheet1.Range("I999").End(xlUp).Offset(1, 0).Value = arrvals(lngrow,4)
                Else
                End If
        End If
    Next lngrow 

End Sub

Actually I would probably build an output array as well, but my thumb is tired...

Upvotes: 0

VBasic2008
VBasic2008

Reputation: 54807

Two Ifs in a Loop

Union Version

Option Explicit

Sub Test1()

    Const cFirst As Integer = 2
    Const cLast As Integer = 999
    Const cCol1 As Variant = "F"
    Const cCol2 As Variant = "C"
    Const cCol3 As Variant = "I"

    Dim i As Integer
    Dim rngU As Range

    With Sheet2
        For i = cFirst To cLast
            If IsNumeric(.Cells(i, cCol1)) And .Cells(i, cCol1) >= 100 Then
                If .Cells(i, cCol2) = "John" _
                        Or .Cells(i, cCol2) = "Tom" Then
                    If Not rngU Is Nothing Then
                        Set rngU = Union(rngU, .Cells(i, cCol1))
                      Else
                        Set rngU = .Cells(i, cCol1)
                    End If
                End If
            End If
        Next
    End With

    If Not rngU Is Nothing Then
        rngU.Copy Sheet1.Cells(cLast, cCol3).End(xlUp).Offset(1, 0)
        Set rngU = Nothing
    End If

End Sub

Upvotes: 1

Alexey C
Alexey C

Reputation: 172

You want something like this?

Sub Test1()

        Dim lRow As Long, r As Long
        lRow = 1000 'last row in your data
        Dim ws As Worksheet
        Set ws = Worksheets("List with your data")

        For i = 2 To lRow
            If ws.Range("F" & i) > 100 Then
                If ws.Range("C" & i).Value = "John" Or ws.Range("C" & i).Value = "Tom" Then
                    Worksheets("Another sheet sheet").Range("A" & r) = Range("C" & i).Value ' r - Row, where we want to enter uor text
                    r = r + 1 'if you want to put next name on the next row
                End If
            End If
        Next

    End Sub

Upvotes: 1

SJR
SJR

Reputation: 23081

Does this solve your problem - referencing the relevant cell in column C? OFFSET provides a relative reference, in this case look 3 columns to the left of F.

Sub Test1()

Dim rngnumbers As Range, rngnames As Range, MultipleRange As Range, numb As Range

Set rngnumbers = Sheet2.Range("F2:F999")
Set rngnames = Sheet2.Range("C2:C999")
Set MultipleRange = Union(rngnumbers, rngnames)

For Each numb In rngnumbers
    If numb.Value >= 100 Then
        If numb.Offset(, -3) = "John" Or numb.Offset(, -3) = "Tom" Then
            Sheet1.Range("I999").End(xlUp).Offset(1, 0).Value = numb.Value
        End If
    End If
Next numb

End Sub

Have you considered SUMIFS instead?

Upvotes: 1

Related Questions