3. Condition

Conditional Statements

Conditional statements are used to perform different actions for different decisions.
In Macros we have four conditional statements:
  • If statement 
  • If...Then...Else statement 
  • If...Then...ElseIf statement
  • Select Case statement 

Here the samples you can refer:

 If statement

Use the If...Then...Else statement if you want to

        Range("L" & d).Select
        If ActiveCell.Value = "" Then
            r = counter - 1
            formula = "=SUM(R[" & r & "]C:R[-1]C)"
            ActiveCell.FormulaR1C1 = formula
            Range("L" & counter).Select
            Selection.NumberFormat = "#,##0.00"
            Range("L" & counter).Select
            Selection.Copy
            Range("M" & counter).Select
            ActiveSheet.Paste
            Range("N" & counter).Select
            ActiveSheet.Paste
            Range("O" & counter).Select
            ActiveSheet.Paste
            Range("P" & counter).Select
            ActiveSheet.Paste
            Range("Q" & counter).Select
            ActiveSheet.Paste
            Range("R" & counter).Select
            ActiveSheet.Paste
        End If

 If...Then...Else statement

        If revenue > MCost Then
            Range("L" & i).Select
            ActiveCell.Value = revenue - MCost    
            Range("M" & i).Select
            ActiveCell.Value = 0                  
        Else
            Range("L" & i).Select
            ActiveCell.Value = 0    'Update Revenue column
            Range("M" & i).Select
            ActiveCell.Value = revenue - MCost * -1                 
        End If

 If...Then...ElseIf statement

You can use the If...Then...ElseIf statement if you want to select one of many blocks of code to execute:

        Range("C" & i).Select
        If ActiveCell.Value <> "2002622" And ActiveCell.Value <> "2000461" Then     
            Rows(i & ":" & i).Select
            Selection.Delete Shift:=xlUp
        ElseIf ActiveCell.Value = "2000461" And STP = "" Then
            Rows(i & ":" & i).Select
            Selection.Delete Shift:=xlUp
        End If

 Select Case statement

You can also use the "Select Case" statement if you want to select one of many blocks of code to execute:

      Range("J" & i).Select
      Select Case ActiveCell.Value
      Case "000010001200000001"
          Range("L" & i).Select                      
          If STP = "" And ActiveCell.Value <> 0 Then 
             Rows(i & ":" & i).Select
             Selection.Delete Shift:=xlUp
          End If
      Case "000020000500000001"
          Range("R" & i).Select
          If STP = "" And ActiveCell.Value = 0 Then 
             Rows(i & ":" & i).Select
             Selection.Delete Shift:=xlUp
          Else
             Range("L" & i).Select
             revenue = ActiveCell.Value
             Range("M" & i).Select
             MCost = ActiveCell.Value
             If revenue > MCost Then
               Range("L" & i).Select
               ActiveCell.Value = revenue - MCost    'Update Revenue column
               Range("M" & i).Select
               ActiveCell.Value = 0                  'Update Material Cost column
             Else
               Range("L" & i).Select
               ActiveCell.Value = 0    'Update Revenue column
               Range("M" & i).Select
               ActiveCell.Value = revenue - MCost * -1                
             End If
             Range("R" & i).Select
             ActiveCell.Value = revenue - MCost    'Update GP column
       End If
      Case "000020000200000001"
          Range("C" & i).Select
          If ActiveCell.Value <> "2002622" And ActiveCell.Value <> "2000461" Then     
              Rows(i & ":" & i).Select
              Selection.Delete Shift:=xlUp
          ElseIf ActiveCell.Value = "2000461" And STP = "" Then
              Rows(i & ":" & i).Select
              Selection.Delete Shift:=xlUp
          End If
      End Select











Comments

Post a Comment

Popular posts from this blog

Excel VBA connect SAPRFC

Image
This is simple and you don't have to install those development tools to run the code. Is just required Microsoft excel you can connect into your SAP R/3. No other tools required, no need the extra software installed and mainly is using Excel VBA run the code and you able to retrieve/import data on your SAP R/3 data. Required item: This is the items required to start your tutorial: Microsoft excel. SAP Login id and password. SAP program with remote function call. I will show your my customized RFC coding in my SAP ABAP. The RFC program name is "ZMM_PRPORS" SAP Gui installed One more setting you have to do before start the coding. You have to add SAP reference below: 1. Go to Developer tab -> Click the Visual Basic: 2. Go to Tools -> References 3. Click those SAP references show as below and click OK. Excel VBA Coding Double click on the sheet1 and pasted code below. Please fill in your SAP ip addres
Read more

How to Create Sap Gui Scripting

Image
SAP GUI Scripting syntax similar like VBScript and you can used setup your testing data or data migration from legacy system to SAP. Is easy and less time to create sap gui scripting compared to LSMW. Sap Gui Scripting it can migrated master data and Transaction data as well. I will showed you how to create it. Here the step by step to create your sap gui script 1. Click on the Script Recording and Playback 2. Click on  More button, you can changed the filename and path. 3. Click on Record button and type "SU01" to lock user id. 4. Once you have completed then click Stop button. 5. Here the script you recorded. If Not IsObject(application) Then    Set SapGuiAuto  = GetObject("SAPGUI")    Set application = SapGuiAuto.GetScriptingEngine End If If Not IsObject(connection) Then    Set connection = application.Children(0) End If If Not IsObject(session) Then    Set session    = connection.Children(0) End If If IsObjec
Read more

Simple macros to re-arrange column into upload format

Image
Excel macros to re-arrange column from text file into rows format. Please watch the video below. 1.  Create 2 Macros name in you excel. 2.  All Macros code have to paste in  Module1. 3.  Copy Macros's code below. Sub Remove() '   Delete title line     Rows("1:5").Select     Selection.Delete Shift:=xlUp      '     For i = 1 To 9000         Range("A" & i).Select         If Left(ActiveCell.Value, 1) = "-" Then             Rows(i & ":" & i).Select             Selection.Delete Shift:=xlUp         End If     Next      '   Delete column B and D     Columns("B:B").Select     Selection.Delete Shift:=xlToLeft          Columns("D:D").Select     Selection.Delete Shift:=xlToLeft          Columns("F:F").Select     Selection.Delete Shift:=xlToLeft '   Paste title in Sheet2     Sheets("Sheet1").Select     Range("A1:A9&q
Read more