Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Application.InputBox If Else help needed

0
I have this macro and it works like i want except I am having issue with one part. it uses this formula in a msgbox "=IFERROR(LEFT(H2,FIND("????",H2)-1),H2)" the user is prompted to change the ???? to the system they are working on. I want a msgbox to pop up if they forget to change the "????" and gives them the option to change it. Below is my complete macro. "Option Explicit [CODE] Sub Check_Parent()   Worksheets(1).Columns("B").EntireColumn.Insert Worksheets(1).Columns("C").EntireColumn.Insert Worksheets(1).Range("B1").Formula = "Value 1" Worksheets(1).Range("C1").Formula = "Value 2" Dim strFormula As String, result As String Dim lr As Long lr = Range("A" & Rows.Count).End(xlUp).Row 'ActiveSheet.Range("B2:B" & lr).Clear Range("B2").Select strFormula = "=IFERROR(LEFT(RC[4],FIND(""????"",RC[4])-1),RC[4])" result = Application.InputBox(prompt:="Change ???? to your System", Title:="Accept Formulas", Default:=strFormula, Type:=0) MsgBox result Columns("B:B").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(1, 1), TrailingMinusNumbers:=True ActiveSheet.Range("C2:C" & lr).Select Selection.FormulaR1C1 = "=IF(RC[-2]=RC[-1],TRUE)" Columns("C").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub"[CODE] Any help would be greatly appreciated
Answer
Discuss

Answers

0

Here is your code, slightly rearranged to ask the user for input. If he enters nothing "???" the code terminates. If he enters something else that string is incorporated into your FIND function and that function string printed to the Immediate pane.

Sub Check_Parent()

    Dim strFormula As String
    Dim Result As String
    Dim lR As Long

    Result = InputBox("What to look for?", "Search criterium", "???")
    If (Len(Result) = 0) Or (Result = "???") Then Exit Sub
    strFormula = "=IFERROR(LEFT(RC[4],FIND(""" & Result & """,RC[4])-1),RC[4])"
    Debug.Print strFormula

    ' addressing the 'Activesheet' usually is a bad a idea
    '   and it's a bad idea here, too.
    '   use code like Worksheets("Sheet1") to name the sheet or 'Worksheets(1) '
    With ActiveSheet
        lR = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range(.Cells(2, "B"), .Cells(lR, "B")).ClearContents
    End With
'    Worksheets(1).Columns("B").EntireColumn.Insert
'    Worksheets(1).Columns("C").EntireColumn.Insert
'    Worksheets(1).Range("B1").Formula = "Value 1"
'    Worksheets(1).Range("C1").Formula = "Value 2"
'    lr = Range("A" & Rows.Count).End(xlUp).Row
'    'ActiveSheet.Range("B2:B" & lr).Clear
'    Range("B2").Select

'    Columns("B:B").Select
'    Selection.Copy
'    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
'    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
'    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
'    TrailingMinusNumbers:=True
'    ActiveSheet.Range("C2:C" & lr).Select
'    Selection.FormulaR1C1 = "=IF(RC[-2]=RC[-1],TRUE)"
'    Columns("C").Select
'    Selection.Copy
'    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Mixing the ways to address sheets is not good. If Worksheets(1) is the ActiveSheet then it should be addressed as Worksheets(1) because addressing the ActiveSheet is dangerous except in a very controlled circumstances. Workshets(1) is the first tab on  the left. The user can change that, too, causing your code to create chaos. Therefore it's best to use sheet namesd in the code.

The rest of your code I neither checked nor examined. However, I saw that you have several Select statements in it. They should be removed. VBA knows where all the parts of the workbook are. Therefore you should use the Range object instead of the Selection object. Define a range by specifying its first and last cells as I have shown you. Then you can do with it whatever you want. Note that code like Range("B2:B" & lR) does indeed specify a range, and Range(Cells(2, "B"), Cells(lR, 2)) does look longer but the latter is more efficient despite of that because Excel uses numbers to identify rows and columns and the range address you take so much pain to create a string of must be converted back to numbers for Excel's use.

Discuss

Discussion

Thanks for your reply but I tried your suggestion and that doesn't even come close to what the macro was doing already. In fact now it does nothing.
ahead May 16, '21 at 9:22 pm
My code shows you an input box and it shows you how to insert the user's input into your formula. It then ouputs the formula to the Immediate pane. To say that it does nothing overlooks the fact that it answers your question.
All the rest of your code is remmed out. The presumption is that it will work same as before once you re-enable it. 
Variatus (rep: 4889) May 17, '21 at 12:05 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login