Run Macro based on String Value ???

J

janki6566

New Member
Joined
Sep 24, 2014
Messages
21
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I wrote the marcro below. I have one key element that I need to make this work and I wanted to see if this is possible. One of the variables is the CoNum (company number). When the Marco runs, the user is asked to enter the three digit company number which in turn I want it to decide whether I run the first part of the macro or second part of the marco. Is this possible?

Code: 
Sub UpdateReconInventory()


'Variable Declarations
Dim CoNum As String
Dim Year As String
Dim Month As String
Dim Day As String
Dim MonthYear As String
Dim MonthDay As String
Dim FilePath As String
Dim CurrFile As String
Dim Answer As String
Dim RowCount As Long




Dim ReconFile As String
    ReconFile = ActiveWorkbook.Name
Dim Stockval As String
    Stockval = "stockval.doc"
Dim Uninvoiced As String
    Uninvoiced = "uninvoiced.doc"
Dim WIP As String
    WIP = "wipval-r.doc"
Dim TrialBal As String
    TrialBal = "trialbalance.doc"
    
Worksheets("Recon").Visible = True
Worksheets("Recon2").Visible = True


'Retrieve Data
CoNum = InputBox("Please input the company number you are reconciling.", "Company", "eg: 140")
Year = InputBox("Please input the year you are reconciling.", "Year", "eg: 2014")
Month = InputBox("Please input the month you are reconciling.", "Month", "eg: 12")
Day = InputBox("Please input the day you are reconciling.", "Date", "eg: 31")


MonthYear = Month & "-" & Right(Year, 2)
MonthDay = Month & Day
FilePath = "\\Erpdb01\DailyReports\c" & CoNum & "\" & Year & "\" & MonthYear & "\" & MonthDay & "\"
    '\\Erpdb01\DailyReports\c140\2014\08-14\0831
    


'TrialBal
Answer = InputBox("Do you want to update the trial balance?  Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
    Workbooks(ReconFile).Worksheets("Trial Balance").Activate
    Range("A:Z").ClearContents
    CurrFile = FilePath & TrialBal
    If Dir(CurrFile) <> "" Then
    Workbooks.Open Filename:=CurrFile
        Range("A:A").Select
        Range("A:A").Copy
        Workbooks(ReconFile).Worksheets("Trial Balance").Activate
        Range("A1").Select
        Range("A1").PasteSpecial xlPasteValues
        Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
                    :="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
        Range("F:F").TextToColumns Destination:=Range("F1"), DataType:=xlFixedWidth, _
                OtherChar:="|", FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
        Workbooks("trialbalance.doc").Close SaveChanges:=False
        Else: GoTo Stockval
End If
End If
    


Stockval:
Answer = InputBox("Do you want to update the Stockval detail?  Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
    Workbooks(ReconFile).Worksheets("Stockval").Activate
    Range("A:Z").ClearContents
    CurrFile = FilePath & Stockval
    If Dir(CurrFile) <> "" Then
    Workbooks.Open Filename:=CurrFile
        Range("A:A").Select
        Range("A:A").Copy
        Workbooks(ReconFile).Worksheets("Stockval").Activate
        Range("A1").Select
        Range("A1").PasteSpecial xlPasteValues
        Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
            :="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
        RowCount = Worksheets("Stockval").Cells(Rows.Count, 1).End(xlUp).Row
        Range("I3").Select
            ActiveCell.FormulaR1C1 = "=IF(ISERROR(SEARCH(""Item Group       : "",RC[-8])),R[-1]C,MID(RC[-8],20,3))"
            Range("I3").Copy
            Range("I3:I" & RowCount).PasteSpecial xlPasteFormulas
        Workbooks("stockval.doc").Close SaveChanges:=False
        Else: GoTo Uninvoiced
End If
End If




Uninvoiced:
Worksheets("Uninvoiced").Columns("A").Hidden = False
Answer = InputBox("Do you want to update the Uninvoiced detail?  Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
    Workbooks(ReconFile).Worksheets("Uninvoiced").Activate
    Range("A:Z").ClearContents
    CurrFile = FilePath & Uninvoiced
    If Dir(CurrFile) <> "" Then
    Workbooks.Open Filename:=CurrFile
        Range("A:A").Select
        Range("A:A").Copy
        Workbooks(ReconFile).Worksheets("Uninvoiced").Activate
        Range("C1").Select
        Range("C1").PasteSpecial xlPasteValues
        Workbooks("uninvoiced.doc").Close SaveChanges:=False
        If Range("C3") <> "" Then
        Range("C:C").TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, _
            FieldInfo:=Array(Array(0, 1), Array(9, 1), Array(18, 1), Array(44, 1), Array(79, 1), _
            Array(89, 1), Array(105, 1), Array(116, 1), Array(131, 1), Array(148, 1), Array(169, 1)), _
            TrailingMinusNumbers:=True
        RowCount = Worksheets("Uninvoiced").Cells(Rows.Count, 3).End(xlUp).Row
        Range("A3").Select
            ActiveCell.FormulaR1C1 = "=IF(ISERROR(SEARCH(""All Cus"",RC[8])),0,RC[11])"
            Range("A3").Copy
            Range("A3:A" & RowCount).PasteSpecial xlPasteFormulas
        Range("B3").Select
            ActiveCell.FormulaR1C1 = _
             "=IFERROR(IF(RIGHT(TRIM(RC[-1]),1)=""-"",LEFT(TRIM(RC[-1]),LEN(TRIM(RC[-1]))-1)*-1,1*TRIM(RC[-1])),0)"
            Range("B3").Copy
            Range("B3:B" & RowCount).PasteSpecial xlPasteFormulas
            Worksheets("Uninvoiced").Columns("A").Hidden = True
            If Range("C3") = "" Then
            
            GoTo WIP
            
        Else: GoTo WIP
End If
End If
End If
End If
    
WIP:
Worksheets("WIP").Columns("A").Hidden = False
Answer = InputBox("Do you want to update the WIP detail?  Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
    Workbooks(ReconFile).Worksheets("WIP").Activate
    Range("A:Z").ClearContents
    CurrFile = FilePath & WIP
    If Dir(CurrFile) <> "" Then
    Workbooks.Open Filename:=CurrFile
        Range("A:A").Select
        Range("A:A").Copy
        Workbooks(ReconFile).Worksheets("WIP").Activate
        Range("C1").Select
        Range("C1").PasteSpecial xlPasteValues
        If Range("C3") <> "" Then
        Range("C:C").TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, _
            FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(39, 1), Array(72, 1), Array(90, 1), _
            Array(106, 1), Array(113, 1), Array(130, 1), Array(148, 1)), TrailingMinusNumbers:= _
            True
        Workbooks("wipval-r.doc").Close SaveChanges:=False
        RowCount = Worksheets("WIP").Cells(Rows.Count, 5).End(xlUp).Row
           Range("A3").Select
            ActiveCell.FormulaR1C1 = "=IF(ISERROR(SEARCH("":"",RC[6])),0,RC[9])"
            Range("A3").Copy
            Range("A3:A" & RowCount).PasteSpecial xlPasteFormulas
        Range("B3").Select
            ActiveCell.FormulaR1C1 = _
             "=IFERROR(IF(RIGHT(TRIM(RC[-1]),1)=""-"",LEFT(TRIM(RC[-1]),LEN(TRIM(RC[-1]))-1)*-1,1*TRIM(RC[-1])),0)"
            Range("B3").Copy
            Range("B3:B" & RowCount).PasteSpecial xlPasteFormulas
             Worksheets("Uninvoiced").Columns("A").Hidden = True
            If Range("C3") = "" Then
            GoTo FinalRecon
End If
End If
End If
End If


FinalRecon:
    Workbooks(ReconFile).Worksheets("Recon").Activate
    Range("C1") = CoNum
    Range("D4") = Month & "-" & Day & "-" & Year
    Worksheets("Recon").Visible = True
End If




'Marco2
'Trial Balance
Answer = InputBox("Do you want to update the trial balance?  Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
    Workbooks(ReconFile).Worksheets("Trial Balance").Activate
    Range("A:Z").ClearContents
    CurrFile = FilePath & TrialBal
        If Dir(CurrFile) <> "" Then
            Workbooks.Open Filename:=CurrFile
            Range("A:A").Select
            Range("A:A").Copy
            Workbooks(ReconFile).Worksheets("Trial Balance").Activate
            Range("A1").Select
            Range("A1").PasteSpecial xlPasteValues
            Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
                    :="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
            Range("F:F").TextToColumns Destination:=Range("F1"), DataType:=xlFixedWidth, _
                OtherChar:="|", FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
            RowCount = Worksheets("Trial Balance").Cells(Rows.Count, 3).End(xlUp).Row
            Range("I3").Select
            ActiveCell.FormulaR1C1 = _
                "=SUBSTITUTE(SUBSTITUTE(RC[-3],""."",""""),"","",""."")"
                Range("I3").Copy
                Range("I3:I" & RowCount).PasteSpecial xlPasteFormulas
            Range("J3").Select
                ActiveCell.FormulaR1C1 = _
                "=IFERROR(IF(RIGHT(TRIM(RC[-1]),1)=""-"",LEFT(TRIM(RC[-1]),LEN(TRIM(RC[-1]))-1)*-1,1*TRIM(RC[-1])),0)"
                Range("J3").Copy
                Range("J3:J" & RowCount).PasteSpecial xlPasteFormulas
         Workbooks("trialbalance.doc").Close SaveChanges:=False
        Else: GoTo Stockval
        End If
    Else: GoTo Stockval
End If
    


Stockval:
Answer = InputBox("Do you want to update the Stockval detail?  Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
    Workbooks(ReconFile).Worksheets("Stockval").Activate
    Range("A:Z").ClearContents
    CurrFile = FilePath & Stockval
     If Dir(CurrFile) <> "" Then
        Workbooks.Open Filename:=CurrFile
        Range("A:A").Select
        Range("A:A").Copy
        Workbooks(ReconFile).Worksheets("Stockval").Activate
        Range("A1").Select
        Range("A1").PasteSpecial xlPasteValues
        Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
            :="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
           Workbooks("stockval.doc").Close SaveChanges:=False
             RowCount = Worksheets("Stockval").Cells(Rows.Count, 3).End(xlUp).Row
            Range("I3").Select
            ActiveCell.FormulaR1C1 = _
                "=SUBSTITUTE(SUBSTITUTE(RC[-1],""."",""""),"","",""."")"
                Range("I3").Copy
                Range("I3:I" & RowCount).PasteSpecial xlPasteFormulas
            Range("J3").Select
                ActiveCell.FormulaR1C1 = _
                "=IFERROR(IF(RIGHT(TRIM(RC[-1]),1)=""-"",LEFT(TRIM(RC[-1]),LEN(TRIM(RC[-1]))-1)*-1,1*TRIM(RC[-1])),0)"
                Range("J3").Copy
                Range("J3:J" & RowCount).PasteSpecial xlPasteFormulas
    Else: GoTo Uninvoiced
    End If
    End If




Uninvoiced:
Worksheets("Uninvoiced").Columns("A").Hidden = False
Answer = InputBox("Do you want to update the Uninvoiced detail?  Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
    Workbooks(ReconFile).Worksheets("Uninvoiced").Activate
    Range("C:AZ").ClearContents
    CurrFile = FilePath & Uninvoiced
    If Dir(CurrFile) <> "" Then
        Workbooks.Open Filename:=CurrFile
        Range("A:A").Select
        Range("A:A").Copy
        Workbooks(ReconFile).Worksheets("Uninvoiced").Activate
        Range("C1").Select
        Range("C1").PasteSpecial xlPasteValues
        Workbooks("uninvoiced.doc").Close SaveChanges:=False
           If Range("C3") <> "" Then
        Range("C:C").TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, _
            FieldInfo:=Array(Array(0, 1), Array(9, 1), Array(18, 1), Array(44, 1), Array(79, 1), _
            Array(89, 1), Array(105, 1), Array(116, 1), Array(131, 1), Array(148, 1), Array(169, 1)), _
            TrailingMinusNumbers:=True
        RowCount = Worksheets("Uninvoiced").Cells(Rows.Count, 3).End(xlUp).Row
        Range("A3").Select
            ActiveCell.FormulaR1C1 = "=IF(ISERROR(SEARCH(""All Cus"",RC[8])),0,RC[11])"
            Range("A3").Copy
            Range("A3:A" & RowCount).PasteSpecial xlPasteFormulas
        Range("B3").Select
            ActiveCell.FormulaR1C1 = _
             "=IFERROR(IF(RIGHT(TRIM(RC[-1]),1)=""-"",LEFT(TRIM(RC[-1]),LEN(TRIM(RC[-1]))-1)*-1,1*TRIM(RC[-1])),0)"
            Range("B3").Copy
            Range("B3:B" & RowCount).PasteSpecial xlPasteFormulas
            Worksheets("Uninvoiced").Columns("A").Hidden = True
            If Range("C3") = "" Then
            
            GoTo WIP
        
   Else: GoTo WIP
    End If
    End If
    End If
    End If
    
WIP:
Worksheets("WIP").Columns("A").Hidden = False
Answer = InputBox("Do you want to update the WIP detail?  Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
    Workbooks(ReconFile).Worksheets("WIP").Activate
    Range("C:AZ").ClearContents
    CurrFile = FilePath & WIP
    If Dir(CurrFile) <> "" Then
        Workbooks.Open Filename:=CurrFile
        Range("A:A").Select
        Range("A:A").Copy
        Workbooks(ReconFile).Worksheets("WIP").Activate
        Range("C1").Select
        Range("C1").PasteSpecial xlPasteValues
         If Range("C3") <> "" Then
        Range("C:C").TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, _
            FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(39, 1), Array(72, 1), Array(90, 1), _
            Array(106, 1), Array(113, 1), Array(130, 1), Array(148, 1)), TrailingMinusNumbers:= _
            True
        Workbooks("wipval-r.doc").Close SaveChanges:=False
        RowCount = Worksheets("WIP").Cells(Rows.Count, 5).End(xlUp).Row
           Range("A3").Select
            ActiveCell.FormulaR1C1 = "=IF(ISERROR(SEARCH("":"",RC[6])),0,RC[9])"
            Range("A3").Copy
            Range("A3:A" & RowCount).PasteSpecial xlPasteFormulas
        Range("B3").Select
            ActiveCell.FormulaR1C1 = _
             "=IFERROR(IF(RIGHT(TRIM(RC[-1]),1)=""-"",LEFT(TRIM(RC[-1]),LEN(TRIM(RC[-1]))-1)*-1,1*TRIM(RC[-1])),0)"
            Range("B3").Copy
            Range("B3:B" & RowCount).PasteSpecial xlPasteFormulas
            Worksheets("WIP").Columns("A").Hidden = True
            If Range("C3") = "" Then
          
    Else: GoTo FinalRecon
    End If
    End If
    End If
    End If




FinalRecon:
    Workbooks(ReconFile).Worksheets("Recon2").Activate
    Range("C1") = CoNum
    Range("D4") = Month & "-" & Day & "-" & Year
    
Worksheets("Recon").Visible = False
Worksheets("Recon2").Visible = True
End Sub
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Can you not just wrap it in an IF statement, sort of like:

Code: 
If CoNum = "whatever your test is" Then
    'code for first part of macro
Else
    'code for last part of macro
End If

This answer seems too simple though so let me know if I've got the wrong end of the stick.
 
Upvote 0
No that was my first idea but for some reason it doesn't work. It just keeps looping the macro over and over and over again leaving me to have to break it.

I tried doing a select case but I am not sure how to do it as right now I have it asking the person which macro they need to do based on their currency style. My boss doesn't want them to even know the difference so my goal was to try and do it within the macro.
Thank you for your help!!!

J
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
ActiveSheet. Paste error
Replies
1
Views
49
RoryA
RoryA
D
  • Question
VBA optimization advice
Replies
3
Views
142
bobsan42
bobsan42
C
  • Question
How to get my macro to stop at the last recognized line
Replies
6
Views
246
Coco1
C
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
1K
Joe4
Joe4
I
  • Question
Current Code taking 10 Hours to run. Need Help to Optimize
Replies
5
Views
281
ItalianPlatinum
I

Forum statistics

Threads
1,215,429
Messages
6,124,843
Members
449,193
Latest member
MikeVol

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