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:
Sort by date Sort by votes
Sorry, looks like you have a space between "Input" and "1", so it should be:
Code: 
If Application.WorksheetFunction.CountIf(Range("E6:AH6"), "Input 1")>0 Then
 
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Joe4 said:
Sorry, looks like you have a space between "Input" and "1", so it should be:
Code: 
If Application.WorksheetFunction[COLOR=#ff0000].CountIf([/COLOR]Range("E6:AH6"), "Input 1")>0 Then
Click to expand...

Red error=Type mismatch

Code: 
[COLOR=#ff0000]If wks Is Application.WorksheetFunction.CountIf(Range("E6:AH6"), "Input 1") > 0 Then[/COLOR]
                    Sheets("Input 1").Visible = True
                    Sheets("Input 2").Visible = True
                    Sheets("Input 3").Visible = True
                    Sheets("Input 4").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
                    
                 End If
 
Last edited:
Upvote 0
Red error=Type mismatch
Click to expand...
Really?
That EXACT expression works just fine for me.
I am at a loss. I do not see any reason why it shouldn't work.
 
Upvote 0
Excel 2016 (Windows) 64 bit

<tbody>
</tbody>
Excel 2016 (Windows) 64 bit
E​
F​
G​
H​
I​
J​
5​
92+36​
925+3​
912+38​
42+39​
962+50​
91+41​
6​
INPUT 1​
INPUT 2​
INPUT 3​
INPUT 4​
INPUT 3​
INPUT 2​
Sheet: Abstract
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 Application.WorksheetFunction.[COLOR=#ff0000]CountIf[/COLOR](Range("E6:AH6"), "INPUT 1") > 0 Then
      
                    Sheets("INPUT 1").Visible = True
                    Sheets("INPUT 2").Visible = True
                    Sheets("INPUT 3").Visible = True
                    Sheets("INPUT 4").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
                    
                 
                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
            Sheets("INPUT 2").Visible = xlSheetVeryHidden
            Sheets("INPUT 3").Visible = xlSheetVeryHidden
            Sheets("INPUT 4").Visible = xlSheetVeryHidden
        Next X
    End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Compile Error Type Mismatch..
Find the Attached table and code
 
Last edited:
Upvote 0
OK. I see you went back and edited your post AFTER I responded to it.
Code: 
[COLOR=#ff0000]If wks Is Application.WorksheetFunction.CountIf(Range("E6:AH6"), "Input 1") > 0 Then[/COLOR]
Why did you add "wks Is" to that line?
That is what is blowing it up.
 
Last edited:
Upvote 0
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
                [COLOR=#ff0000]'Might need help here to fix this macro here,i have added Your code here this code got stopped with [B][U]compile error List seperator )[/U][/B]
                If wks Is [/COLOR][COLOR=#0000ff]Nothing Then
                if[/COLOR][COLOR=#ff0000] Application.WorksheetFunction.CountIf(Range("E6:AH6"), "INPUT 1") > 0 Then[/COLOR]
      
                    Sheets("INPUT 1").Visible = True
                    Sheets("INPUT 2").Visible = True
                    Sheets("INPUT 3").Visible = True
                    Sheets("INPUT 4").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
                    
                 
                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
            Sheets("INPUT 2").Visible = xlSheetVeryHidden
            Sheets("INPUT 3").Visible = xlSheetVeryHidden
            Sheets("INPUT 4").Visible = xlSheetVeryHidden
        Next X
    End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
I dont know where to use your statement in my macro in a proper manner.could you please help me with full macro it would be helpful.
 
Upvote 0
Any Help @joe4?
iam waiting..
Click to expand...
I am sorry, but I am usually not around much on the weekends. Family events usually take us most of my time on the weekends.

I don't think I can provide any more help on this, as I don't really fully follow what you code is supposed to be doing. I answered the original question about how to find if a certain value exists in a certain range. But I do not understand how you are trying to use this in your code, so I cannot tell you where to put it in your code.
 
Upvote 0
iam looking for help can you please assign some one for me to solve my issue or else iam ready to pay for this issue..its very important task for me.
 
Last edited by a moderator:
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
auto repeating the dates in specific cells in column by writing in two input box
Replies
3
Views
118
mumps
mumps
VinceF
  • Solved
Circular Reference Error
2
Replies
10
Views
414
mumps
mumps
A
  • Solved
Help Changing Cell Color to Based of Date referring to a specific Interior Cell Color
Replies
8
Views
321
SanjayGMusafir
SanjayGMusafir
T
  • Solved
Index match with multiple criteria, sheets & columns
2
Replies
10
Views
435
Fluff
Fluff
D
  • Question
Adapt IF AND ISBLANK formula combination in relation to UNIQUE FILTER on another sheet
2
Replies
15
Views
648
KRice
K

Forum statistics

Threads
1,215,219
Messages
6,123,689
Members
449,117
Latest member
Aaagu

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