VBA Private Sub Application for IF Statement

[Pinaceous]

Hi All,

I'm working on a private sub that will perform upon an If Statement.

Where I'm working with Column H & I over Range("H11:I180").

Right now, if I have :

If Range("H11") = "Enter Tally" And Range("I11") = "" Then

'Clear contents of both of these cells.

End If

I'm looking to clear the contents of both of these cells if this condition comes up and to apply its code over the range for both of these columns H&I.

Please let me know, if you can help.

Thank you!
Pinaceous

 

[johnnyL]

Not sure what you are asking for.

Do you want that entire range cleared If Range("H11") = "Enter Tally" And Range("I11") = ""
Or
Are you wanting to check H12 / I12, H13 / I13, etc?

 

[Pinaceous]

Hi johnnyL,

"Where I'm working with Column H & I over Range("H11:I180")."

If this happens for

If Range("H11") = "Enter Tally" And Range("I11") = "" Then

clear cell H11 & I11.

If this happens for

If Range("H12") = "Enter Tally" And Range("I12") = "" Then

clear cell H12 & I12.

.....

If this happens for

If Range("H180") = "Enter Tally" And Range("I180") = "" Then

clear cell H180 & I180.

I hope this is clearer.

Thank you for responding!
R/
Pinaceous

 

[johnnyL]

How about:

Sub Test()
'
    Dim ArrayRow    As Long
    Dim EndRow      As Long, StartRow           As Long
    Dim LoopCount   As Long
    Dim InputArray  As Variant, OutputArray()   As Variant
'
    EndRow = 180
    StartRow = 11
'
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
'
    InputArray = Range("H" & StartRow & ":I" & EndRow).Value
    ReDim OutputArray(1 To UBound(InputArray, 1), 1 To UBound(InputArray, 2))
'
    ArrayRow = 0
'
    For LoopCount = StartRow To EndRow
        ArrayRow = ArrayRow + 1
'
        If InputArray(ArrayRow, 1) = "Enter Tally" And InputArray(ArrayRow, 2) = "" Then
            OutputArray(ArrayRow, 1) = ""
            OutputArray(ArrayRow, 2) = ""
        Else
            OutputArray(ArrayRow, 1) = InputArray(ArrayRow, 1)
            OutputArray(ArrayRow, 2) = InputArray(ArrayRow, 2)
        End If
    Next
'
    Range("H" & StartRow & ":I" & EndRow) = OutputArray
'
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

 

[Pinaceous]

Wow johnnyL!

Many thanks for providing me with your code!

I'll test it out and get back!

Thanks again!
pinaceous

 

[morgoyo]

How about:

Sub Test()
'
    Dim ArrayRow    As Long
    Dim EndRow      As Long, StartRow           As Long
    Dim LoopCount   As Long
    Dim InputArray  As Variant, OutputArray()   As Variant
'
    EndRow = 180
    StartRow = 11
'
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
'
    InputArray = Range("H" & StartRow & ":I" & EndRow).Value
    ReDim OutputArray(1 To UBound(InputArray, 1), 1 To UBound(InputArray, 2))
'
    ArrayRow = 0
'
    For LoopCount = StartRow To EndRow
        ArrayRow = ArrayRow + 1
'
        If InputArray(ArrayRow, 1) = "Enter Tally" And InputArray(ArrayRow, 2) = "" Then
            OutputArray(ArrayRow, 1) = ""
            OutputArray(ArrayRow, 2) = ""
        Else
            OutputArray(ArrayRow, 1) = InputArray(ArrayRow, 1)
            OutputArray(ArrayRow, 2) = InputArray(ArrayRow, 2)
        End If
    Next
'
    Range("H" & StartRow & ":I" & EndRow) = OutputArray
'
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Hey Johnny, I am an Excel newbie. May I ask why not use just a simple loop like this:

For i = 11 To 180
    
    If Range("H" & i) = "Enter Tally" And Range("I" & i) = "" Then

        Cells(i, "H").Clear
        Cells(i, "I").Clear

    Else
    End If
Next

 

[johnnyL]

May I ask why not use just a simple loop like this:

The code I submitted does one read from the sheet and one write to the sheet.
With Excel, the more times you read from a sheet and more times you write to a sheet, the time required to complete increases.

You could do a simple loop:

    For i = 11 To 180
        If Range("H" & i) = "Enter Tally" And Range("I" & i) = "" Then
            Range("H" & i & ":I" & i).ClearContents
        End If
    Next

but as I mentioned, that would access the sheet hundreds of times.

 

[morgoyo]

The code I submitted does one read from the sheet and one write to the sheet.
With Excel, the more times you read from a sheet and more times you write to a sheet, the time required to complete increases.

You could do a simple loop:

    For i = 11 To 180
        If Range("H" & i) = "Enter Tally" And Range("I" & i) = "" Then
            Range("H" & i & ":I" & i).ClearContents
        End If
    Next

but as I mentioned, that would access the sheet hundreds of times.

Great, thanks! It seemed to me like that code also has a loop to read every row separately so I didn't understand it

 

[johnnyL]

Great, thanks! It seemed to me like that code also has a loop to read every row separately so I didn't understand it

If you are referring to the code in post #4, there is a loop to read every 'row' but it is reading the values in the array that were saved from the sheet.
The array values are in memory so they are read much faster than excel can read from a sheet.
An output array is created to store what the final values will be, and when done, the output array is written to the sheet to display the final values.

So one read from the sheet to get all values into the first array, then one write, of the second array, to the sheet.

 

[morgoyo]

If you are referring to the code in post #4, there is a loop to read every 'row' but it is reading the values in the array that were saved from the sheet.
The array values are in memory so they are read much faster than excel can read from a sheet.
An output array is created to store what the final values will be, and when done, the output array is written to the sheet to display the final values.

So one read from the sheet to get all values into the first array, then one write, of the second array, to the sheet.

Wow... Now, I understand it. That's great! Thank you