PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Tuesday, November 22, 2022

[FIXED] How to give multiple conditions in if statement in VBA

 November 22, 2022     excel, foreach, if-statement, multiple-conditions, vba     No comments   

Issue

I have written a VBA code to auto hide rows when the three column " D" , "E" and "F" simultaneously show zero value. If any one of them is having non-zero value it should not hide the rows. Moreover, I also want to add "#N/A" also, like if #NA is there just like Zero it should hide the row.

But it seems there is some problem with the code.

Sub HideRows()
Application.ScreenUpdating = False
Application.Calculation = xlManual
 
For Each x In Range("D2:D2000")
Next
For Each y In Range("E2:E2000")
Next
For Each Z In Range("F2:F2000")
Next
If x.Value = 0 And y.Value = 0 And Z.Value = 0 Then x.EntireRow.Hidden = True

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

Solution

Welcome to the site!

Your syntax is not correct. The loop For Each ... Next should encompass the code that you want to repeat . Since your loop does not contain anything inside, it does absolutely nothing. I tried to write an understandable code for you:

Sub HideRows()
Dim i as Long 'Counter
Application.ScreenUpdating = False
Application.Calculation = xlManual

With ActiveSheet
    For i = 2 To 2000
        If (.Range("D" & i).Value = 0 Or .Range("D" & i).Text = "#N/A") And _
           (.Range("E" & i).Value = 0 Or .Range("E" & i).Text = "#N/A") And _
           (.Range("F" & i).Value = 0 Or .Range("F" & i).Text = "#N/A") Then
        .Range("D" & i).EntireRow.Hidden = True
        End If
    Next
End With

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

In the example you see that the loop For ... Next encompasses some code, therefore the code will be done, as you can see, for 1999 times counting from 2 to 2000.Everything is enclosed in With ActiveSheet ... End With statement. The reason is to make the code more readable and not to repeat ActiveSheet. If you did not use it, instead of .Range you would have to write the full thing (ActiveSheet.Range).



Answered By - ZygD
Answer Checked By - David Goodson (PHPFixing Volunteer)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing