Help me with IF Statement

B

bhandari

Active Member
Joined
Oct 17, 2017
Messages
359
i want to add IF condition in my macro.This condition need to merge in my code
if E5= Random Value
E6=Data List Validation (INPUT 1,INPUT 2,INPUT 3,INPUT 4)
if E6=INPUT 1
ElseIf
E6=INPUT 2
ElseIf
E6=INPUT 3
ElseIf
E6=INPUT 4
(CODE IS SAME ONLY FORMULAS ARE CHANGING IF INPUT 1 THEN THIS FORMULAS.ITS DEPEND UPON E6 CELL INPUT) i will add my formulas for it)
I have Given my if condition code is working fine but i need to add above condition in my macro:)
Code: 
 If Len(.Cells(5, X).Value) > 0 Then
                On Error Resume Next
                Set wks = Sheets(CStr(.Cells(5, X).Value))
                On Error GoTo 0
                If wks Is Nothing Then
                    Sheets("INPUT 1").Visible = True
                    Sheets("Shapes").Visible = True
                    Sheets("INPUT 1").Copy after:=Sheets(Sheets.Count)
                    Sheets(Sheets.Count).Name = .Cells(5, X).Value
                    Set wks = ActiveSheet
                     MyFormulas = Array("='" & wks.Name & "'!$BB$105", "", "", "",  "='" & wks.Name & "'!$BB$109", "", "", "", "='" & wks.Name  & "'!$BB$124", "='" & wks.Name & "'!$BB$143", "", "", "",  "", "", "='" & wks.Name & "'!$BB$115+'" & wks.Name &  "'!$BB$116", "", "='" & wks.Name & "'!$BB$211", "", "", "", "",  "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",  "", "", "", "", "", "", "", "", "", "='" & wks.Name &  "'!$BB$151", "", "='" & wks.Name & "'!$BB$157", "", "", "", "",  "", "", "", "='" & wks.Name & "'!$BB$167", "", "='" &  wks.Name & "'!$BB$169", "='" & wks.Name & "'!$BB$168", "='"  & wks.Name & "'!$BB$160", "", "", "='" & wks.Name &  "'!$BB$183", "='" & wks.Name & "'!$BB$193", "", "", "='" &  wks.Name & "'!$BB$187", "", "", "", "", "='" & wks.Name &  "'!$BB$192", "", "", "", "='" & wks.Name & "'!$BB$158", "='"  & wks.Name & "'!$BB$159", "='" & wks.Name & "'!$BB$200",  "='" & wks.Name & "'!$BB$195", "", "", "", "='" & wks.Name  & "'!$BB$203", "", "", "", "", "" _
                    &  "='" & wks.Name & "'!$BB$196", "", "", "", "='" & wks.Name  & "'!$BB$199", "", "", "", "", "", "", "", "", "", "", "", "", "",  "", "", "", "", "", "", "", "", "", "='" & wks.Name &  "'!$BB$212*25", "='", "='")
                    .Range("A10").Offset(, X - 1).Resize(119, 1).Formula = Application.Transpose(MyFormulas)
                    ThisWorkbook.Worksheets("Abstract").Range("E130:AH130").Clear
                Else
                     MsgBox "Sheets: " & .Cells(5, X).Value & vbCrLf & vbCrLf  & "Already exists!", vbExclamation, "Sheet Exists"
                End If
            End If
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Code: 
            If Len(.Cells(5, X).Value) > 0 Then
                On Error Resume Next
                Set wks = Sheets(CStr(.Cells(5, X).Value))
                On Error GoTo 0
               
                If wks Is Nothing Then
                 [COLOR=#ff0000]If Range("E6:AH6") = "INPUT 1" And Range("E6:AH6") = "INPUT 2" And Range("E6:AH6") = "INPUT 3" And Range("E6:AH6") = "INPUT 4" Then[/COLOR]
                    Sheets("[COLOR=#ff0000]INPUT 1[/COLOR]").Visible = True
                    Sheets("[COLOR=#ff0000]INPUT 2[/COLOR]").Visible = True
                    Sheets("[COLOR=#ff0000]INPUT 3[/COLOR]").Visible = True
                    Sheets("[COLOR=#ff0000]INPUT 4[/COLOR]").Visible = True
                    Sheets("Shapes").Visible = True
                    Sheets("Box Girder").Copy after:=Sheets(Sheets.Count)
                    Sheets(Sheets.Count).Name = .Cells(5, X).Value
                    Set wks = ActiveSheet
                    MyFormulas = Array("='" & wks.Name & "'!$BB$105", "", "", "", "='" & wks.Name & "'!$BB$109", "", "", "", "='" & wks.Name & "'!$BB$124", "='" & wks.Name & "'!$BB$143", "", "", "", "", "", "='" & wks.Name & "'!$BB$115+'" & wks.Name & "'!$BB$116", "", "='" & wks.Name & "'!$BB$211", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "='" & wks.Name & "'!$BB$151", "", "='" & wks.Name & "'!$BB$157", "", "", "", "", "", "", "", "='" & wks.Name & "'!$BB$167", "", "='" & wks.Name & "'!$BB$169", "='" & wks.Name & "'!$BB$168", "='" & wks.Name & "'!$BB$160", "", "", "='" & wks.Name & "'!$BB$183", "='" & wks.Name & "'!$BB$193", "", "", "='" & wks.Name & "'!$BB$187", "", "", "", "", "='" & wks.Name & "'!$BB$192", "", "", "", "='" & wks.Name & "'!$BB$158", "='" & wks.Name & "'!$BB$159", "='" & wks.Name & "'!$BB$200", "='" & wks.Name & "'!$BB$195", "", "", "", "='" & wks.Name & "'!$BB$203", "", "", "", "", "" _
                    & "='" & wks.Name & "'!$BB$196", "", "", "", "='" & wks.Name & "'!$BB$199", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "='" & wks.Name & "'!$BB$212*25", "='", "='")
                    .Range("A10").Offset(, X - 1).Resize(119, 1).Formula = Application.Transpose(MyFormulas)
                    ThisWorkbook.Worksheets("Abstract").Range("E130:AH130").Clear
                 [COLOR=#ff0000]End If[/COLOR]
                Else
                    MsgBox "Sheets: " & .Cells(5, X).Value & vbCrLf & vbCrLf & "Already exists!", vbExclamation, "Sheet Exists"
                End If
            End If
Iam getting Error can any one help me with this code
 
Last edited:
Upvote 0
If Range("E6:AH6") = "INPUT 1" And Range("E6:AH6") = "INPUT 2" And Range("E6:AH6") = "INPUT 3" And Range("E6:AH6") = "INPUT 4" Then
Click to expand...
You cannot check for a range range of values (i.e. E6:AH6) is equal to a single value; that doesn't make any sense.
You can only check if a single cell is equal to a specific value. If you want to check each one, you will need to loop through that range.

The question is, what are you trying to do there?
Are you trying to check to see if ANY cell in that range is equal to that value, or id ALL the cells in that range are equal to that value?
 
Last edited:
Upvote 0
https://drive.google.com/open?id=1CywBgnhX-3j-pRNl0OOUbRMKSQQ5lgFk


https://www.dropbox.com/s/hha5wwgu629s8px/Untitled.png?dl=0

if any possible way of attachment of image let me know

IF E5=E6 cells are matched Similarly F5=F6,G5=G6 Then proper sheet need to open

E6,F6,G6...UP TO AH6= data Validation List(INPUT 1, INPUT 2,INPUT 3,INPUT 4)(Sheet tab names)

User can fill the range in E6..IF you open the picture link you will understand this easily.
Code: 
 If Len(.Cells(5, X).Value) > 0 Then
                On Error Resume Next
                Set wks = Sheets(CStr(.Cells(5, X).Value))
                On Error GoTo 0
                If wks Is Nothing Then
                    Sheets("INPUT 1").Visible = True
                    Sheets("Shapes").Visible = True
                    Sheets("INPUT 1").Copy after:=Sheets(Sheets.Count)
                    Sheets(Sheets.Count).Name = .Cells(5, X).Value
                    Set wks = ActiveSheet
                     MyFormulas = Array("='" & wks.Name & "'!$BB$105", "", "", "",  "='" & wks.Name & "'!$BB$109", "", "", "", "='" & wks.Name  & "'!$BB$124", "='" & wks.Name & "'!$BB$143", "", "", "",  "", "", "='" & wks.Name & "'!$BB$115+'" & wks.Name &  "'!$BB$116", "", "='" & wks.Name & "'!$BB$211", "", "", "", "",  "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",  "", "", "", "", "", "", "", "", "", "='" & wks.Name &  "'!$BB$151", "", "='" & wks.Name & "'!$BB$157", "", "", "", "",  "", "", "", "='" & wks.Name & "'!$BB$167", "", "='" &  wks.Name & "'!$BB$169", "='" & wks.Name & "'!$BB$168", "='"  & wks.Name & "'!$BB$160", "", "", "='" & wks.Name &  "'!$BB$183", "='" & wks.Name & "'!$BB$193", "", "", "='" &  wks.Name & "'!$BB$187", "", "", "", "", "='" & wks.Name &  "'!$BB$192", "", "", "", "='" & wks.Name & "'!$BB$158", "='"  & wks.Name & "'!$BB$159", "='" & wks.Name & "'!$BB$200",  "='" & wks.Name & "'!$BB$195", "", "", "", "='" & wks.Name  & "'!$BB$203", "", "", "", "", "" _
                    &  "='" & wks.Name & "'!$BB$196", "", "", "", "='" & wks.Name  & "'!$BB$199", "", "", "", "", "", "", "", "", "", "", "", "", "",  "", "", "", "", "", "", "", "", "", "='" & wks.Name &  "'!$BB$212*25", "='", "='")
                    .Range("A10").Offset(, X - 1).Resize(119, 1).Formula = Application.Transpose(MyFormulas)
                    ThisWorkbook.Worksheets("Abstract").Range("E130:AH130").Clear
                Else
                     MsgBox "Sheets: " & .Cells(5, X).Value & vbCrLf & vbCrLf  & "Already exists!", vbExclamation, "Sheet Exists"
                End If
            End If
This code is working good for only INPUT 1
IF i give E6=INPUT 2
INPUT 2 sheet need to Open from hidden sheet

Here is my Full code
Code: 
Sub CreateSheets()
    
    Dim X           As Long
    Dim wks         As Worksheet
    Dim MyFormulas  As Variant
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlAutomatic
    
    With Sheets("Abstract")
        For X = 5 To .Cells(5, Columns.Count).End(xlToLeft).Column
            If Len(.Cells(5, X).Value) > 0 Then
                On Error Resume Next
                Set wks = Sheets(CStr(.Cells(5, X).Value))
                On Error GoTo 0
                If wks Is Nothing Then
                    Sheets("INPUT 1").Visible = True
                    Sheets("Shapes").Visible = True
                    Sheets("INPUT 1").Copy after:=Sheets(Sheets.Count)
                    Sheets(Sheets.Count).Name = .Cells(5, X).Value
                    Set wks = ActiveSheet
                    MyFormulas = Array("='" & wks.Name & "'!$BB$105", "", "", "", "='" & wks.Name & "'!$BB$109", "", "", "", "='" & wks.Name & "'!$BB$124", "='" & wks.Name & "'!$BB$143", "", "", "", "", "", "='" & wks.Name & "'!$BB$115+'" & wks.Name & "'!$BB$116", "", "='" & wks.Name & "'!$BB$211", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "='" & wks.Name & "'!$BB$151", "", "='" & wks.Name & "'!$BB$157", "", "", "", "", "", "", "", "='" & wks.Name & "'!$BB$167", "", "='" & wks.Name & "'!$BB$169", "='" & wks.Name & "'!$BB$168", "='" & wks.Name & "'!$BB$160", "", "", "='" & wks.Name & "'!$BB$183", "='" & wks.Name & "'!$BB$193", "", "", "='" & wks.Name & "'!$BB$187", "", "", "", "", "='" & wks.Name & "'!$BB$192", "", "", "", "='" & wks.Name & "'!$BB$158", "='" & wks.Name & "'!$BB$159", "='" & wks.Name & "'!$BB$200", "='" & wks.Name & "'!$BB$195", "", "", "", "='" & wks.Name & "'!$BB$203", "", "", "", "", "" _
                    & "='" & wks.Name & "'!$BB$196", "", "", "", "='" & wks.Name & "'!$BB$199", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "='" & wks.Name & "'!$BB$212*25", "='", "='")
                    .Range("A10").Offset(, X - 1).Resize(119, 1).Formula = Application.Transpose(MyFormulas)
                    ThisWorkbook.Worksheets("Abstract").Range("E130:AH130").Clear
                Else
                    MsgBox "Sheets: " & .Cells(5, X).Value & vbCrLf & vbCrLf & "Already exists!", vbExclamation, "Sheet Exists"
                End If
            End If
            ActiveSheet.Protect Password:="stayaway", userinterfaceonly:=True
            Dim shape As shape
            For Each shape In ActiveSheet.Shapes
              shape.Locked = True
            Next
            Set wks = Nothing
            Sheets("INPUT 1").Visible = xlSheetVeryHidden
            Sheets("Shapes").Visible = xlSheetVeryHidden

            
        Next X
    End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
if any possible way of attachment of image let me know
Click to expand...

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

My reply was in regards to your second post, asking about the error.
You did not answer the question I asked there:
Are you trying to check to see if ANY cell in that range is equal to that value, or id ALL the cells in that range are equal to that value?
Click to expand...
Meaning, do you want to check to see if "Input1" is found anywhere in cells E6:AH6, or if "Input1" is found in ALL the cells in that range?

I really cannot say I follow the rest of your code and what you are doing, especially since you have only given us a small snippet of your code.
Your use of .Cells indicates that this is just part of a bigger With statement.
 
Last edited:
Upvote 0
open
 
Last edited:
Upvote 0
iam trying to check to see if ANY cell in that range is equal to that value
Click to expand...
Here is one way:
Code: 
If Application.WorksheetFunction.CountIf(Range("E6:AH6"), "Input1")>0 Then
 
Upvote 0
Joe4 said:
Here is one way:
Code: 
If Application.WorksheetFunction.CountIf(Range("E6:AH6"), "Input1")>0 Then
Click to expand...

not working in my code,if possible can u please merge your code with my code,i can understand easily
#5 2nd code need to modify

if Input 1 found
Code: 
If wks Is Nothing Then
                    Sheets("INPUT 1").Visible = True
                    Sheets("Shapes").Visible = True
                    Sheets("INPUT 1").Copy after:=Sheets(Sheets.Count)
                    Sheets(Sheets.Count).Name = .Cells(5, X).Value
                    Set wks = ActiveSheet
                    MyFormulas = Array("='" & wks.Name & "'!$BB$105", "", "", "", "='" & wks.Name & "'!$BB$109", "", "", "", "='" & wks.Name & "'!$BB$124", "='" & wks.Name & "'!$BB$143", "", "", "", "", "", "='" & wks.Name & "'!$BB$115+'" & wks.Name & "'!$BB$116", "", "='" & wks.Name & "'!$BB$211", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "='" & wks.Name & "'!$BB$151", "", "='" & wks.Name & "'!$BB$157", "", "", "", "", "", "", "", "='" & wks.Name & "'!$BB$167", "", "='" & wks.Name & "'!$BB$169", "='" & wks.Name & "'!$BB$168", "='" & wks.Name & "'!$BB$160", "", "", "='" & wks.Name & "'!$BB$183", "='" & wks.Name & "'!$BB$193", "", "", "='" & wks.Name & "'!$BB$187", "", "", "", "", "='" & wks.Name & "'!$BB$192", "", "", "", "='" & wks.Name & "'!$BB$158", "='" & wks.Name & "'!$BB$159", "='" & wks.Name & "'!$BB$200", "='" & wks.Name & "'!$BB$195", "", "", "", "='" & wks.Name & "'!$BB$203", "", "", "", "", "" _
                    & "='" & wks.Name & "'!$BB$196", "", "", "", "='" & wks.Name & "'!$BB$199", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "='" & wks.Name & "'!$BB$212*25", "='", "='")
                    .Range("A10").Offset(, X - 1).Resize(119, 1).Formula = Application.Transpose(MyFormulas)
                    ThisWorkbook.Worksheets("Abstract").Range("E130:AH130").Clear
                Else
                    MsgBox "Sheets: " & .Cells(5, X).Value & vbCrLf & vbCrLf & "Already exists!", vbExclamation, "Sheet Exists"
                End If
This statements need to work

If input 2 found in that range
Code: 
If wks Is Nothing Then
                    Sheets("INPUT 2").Visible = True
                    Sheets("Shapes").Visible = True
                    Sheets("INPUT 2").Copy after:=Sheets(Sheets.Count)
                    Sheets(Sheets.Count).Name = .Cells(5, X).Value
                    Set wks = ActiveSheet
                    MyFormulas = Array("='" & wks.Name & "'!$BB$105", "", "", "", "='" & wks.Name & "'!$BB$109", "", "", "", "='" & wks.Name & "'!$BB$124", "='" & wks.Name & "'!$BB$143", "", "", "", "", "", "='" & wks.Name & "'!$BB$115+'" & wks.Name & "'!$BB$116", "", "='" & wks.Name & "'!$BB$211", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "='" & wks.Name & "'!$BB$151", "", "='" & wks.Name & "'!$BB$157", "", "", "", "", "", "", "", "='" & wks.Name & "'!$BB$167", "", "='" & wks.Name & "'!$BB$169", "='" & wks.Name & "'!$BB$168", "='" & wks.Name & "'!$BB$160", "", "", "='" & wks.Name & "'!$BB$183", "='" & wks.Name & "'!$BB$193", "", "", "='" & wks.Name & "'!$BB$187", "", "", "", "", "='" & wks.Name & "'!$BB$192", "", "", "", "='" & wks.Name & "'!$BB$158", "='" & wks.Name & "'!$BB$159", "='" & wks.Name & "'!$BB$200", "='" & wks.Name & "'!$BB$195", "", "", "", "='" & wks.Name & "'!$BB$203", "", "", "", "", "" _
                    & "='" & wks.Name & "'!$BB$196", "", "", "", "='" & wks.Name & "'!$BB$199", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "='" & wks.Name & "'!$BB$212*25", "='", "='")
                    .Range("A10").Offset(, X - 1).Resize(119, 1).Formula = Application.Transpose(MyFormulas)
                    ThisWorkbook.Worksheets("Abstract").Range("E130:AH130").Clear
                Else
                    MsgBox "Sheets: " & .Cells(5, X).Value & vbCrLf & vbCrLf & "Already exists!", vbExclamation, "Sheet Exists"
                End If
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Need Help VBA
2
Replies
10
Views
437
Trebor76
T
D
  • Question Question
Clinching NHL Playoff Spot
Replies
2
Views
74
dotcanada
D
M
  • Question Question
Copied sheet code to new file and lost some functionality....not sure how/why
Replies
5
Views
125
MeaclH
M
T
  • Solved
Private Sub Worksheet_Change(ByVal Target As Range) with multiple targets
Replies
9
Views
411
TheHighlander
T
B
  • Question Question
Unexperienced VBA user, trying to create a Repo Schedule for use at my office
Replies
1
Views
284
arun_eagertolearn
A

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top