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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
[TABLE="class: head"]
<tbody>[TR]
[/TR]
[TR]

[/TR]
[TR]
[TD="bgcolor: #FFC000"][/TD]
[/TR]
</tbody>[/TABLE]
Excel 2016 (Windows) 64 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][th]
J​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td=bgcolor:#00B050]
92+36​
[/td][td=bgcolor:#00B050]
925+3​
[/td][td=bgcolor:#00B050]
912+38​
[/td][td=bgcolor:#00B050]
42+39​
[/td][td=bgcolor:#00B050]
962+50​
[/td][td=bgcolor:#00B050]
91+41​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td=bgcolor:#FFC000]
INPUT 1​
[/td][td=bgcolor:#FFC000]
INPUT 2​
[/td][td=bgcolor:#FFC000]
INPUT 3​
[/td][td=bgcolor:#FFC000]
INPUT 4​
[/td][td=bgcolor:#FFC000]
INPUT 3​
[/td][td=bgcolor:#FFC000]
INPUT 2​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Abstract[/td][/tr][/table]
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

C
  • Solved
Need Help VBA
2
Replies
10
Views
410
Trebor76
T
T
  • Solved
Private Sub Worksheet_Change(ByVal Target As Range) with multiple targets
Replies
9
Views
245
TheHighlander
T
B
  • Question Question
Unexperienced VBA user, trying to create a Repo Schedule for use at my office
Replies
1
Views
253
arun_eagertolearn
A
M
  • Solved
Optimizing my rows to send e-mail to different address
Replies
2
Views
163
MasterBash
M
P
  • Solved
Not able to correct filter data date wise, Kindly correct my formula...
Replies
1
Views
169
Anthony47
A

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

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