• Ask a Question
150
Insert Image Size must be less than < 5MB.
    Ask a Question
    Cancel
    150
    More answer You can create 5 answer(s).
      Ask a Poll
      Cancel
      Top Contributor

      Use Left, or , and together in VBA Macro

      Madam/Sir,

      I have to check  in Column A whether any number starts with 113 or 114 and the corresponding value for that cell in Column B is positive or negative and if it is negative, then in Column C against that cell enter value as A. Otherwise Column C should be blank.

      I wrote the macro given below, but it is not working.

      Sub Example()
      Dim c As Range
      For Each c In Range("a1:a5")
      For Each d In Range("b1:b5")
      For Each e In Range("c1:c5")
      If c.Value = "" Then
      e.Value = ""
      Else
      If ((Left(c.Value, 3) = 113 Or Left(c.Value, 3) = 114)) And sgn (d.Value) = -1 Then
      e.Value = "A"
      End If
      End If
      Next
      Next
      Next
      End Sub

      Can anybody help please.

      Thanks in advance……

      Asked by Praveen on March 20, 2017 in VBA: Macros.
      1 Answers
      Keymaster

      Hi, Here is the updated VBA Macro fro your requirement:

       

      In this macro, we are looping through from 1 to 5. And Checking if A1 is starts with 113 or 114, if B1 is a negative value.

      If this meets the condition we are printing letter “A” in C1 other wise Blank.

      The loop will continue check the A2,A3..up to A5.

      Sub VBAF1_CheckCellsIfMeetsConditions()
      Dim c As Range
      'Loop through From Row 1 to 5
      For iCntr = 1 To 5
      'Check if its meeting the criterian
      If (Left(Range("A" & iCntr), 3) = 113 Or Left(Range("A" & iCntr), 3) = 114) And Range("B" & iCntr) < 0 Then
      Range("C" & iCntr) = "A" 'Enter A if meets
      Else
      Range("C" & iCntr) = "" 'Other wise Blank
      End If
      Next
      End Sub
      

      Thanks!

      Answered by PNRao on March 20, 2017..
      • Thanks for the detailed and informative answer………

        on March 20, 2017.
      Cancel
      Add comment