Results 1 to 2 of 2

Help!! Vba code correction

This is a discussion on Help!! Vba code correction within the Excel Questions forums, part of the Question Forums category; Hello all, Schedule Details Impact Verification validation a 1 @ one y b 2 # two y c 3 $ ...

  1. #1
    New Member
    Join Date
    Jun 2013
    Posts
    2

    Question Help!! Vba code correction

    Hello all,



    Schedule Details Impact Verification validation
    a 1 @ one y
    b 2 # two y
    c 3 $ three n
    d 4 % four n



    The problem with the code below is it excutes the output for all the cells without validating the condition in column E.

    Any help to rectify this problem will be appreciated.
    I only want the output for cells that have the validation as y

    I have this code in VBA as follows

    Sub validate()
    For Each c In Range("E1:E20")
    If c.Value = "y" Then Macro1
    Next c
    End Sub

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    Dim rngCopy() As Variant
    Dim rngPaste As Range
    Dim Heading As Variant
    Dim Cell As Range
    Dim r As Integer
    Heading = Array("Schedule", "Details", "Impact", "Verification")
    r = 0

    For Each Cell In Sheets(1).Range("A2", Sheets(1).Range("A" & Rows.Count).End(xlUp))
    rngCopy() = Sheets(1).Range(Cell, Cell.Offset(0, 4)).Value
    Set rngPaste = Sheets(2).Range("B" & 2 + r).Resize(4, 1)
    rngPaste = Application.Transpose(rngCopy)
    Sheets(2).Range("A" & 2 + r & ":A" & 5 + r).Value = Application.Transpose(Heading)
    r = r + 5
    Next Cell
    End Sub

  2. #2
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    11,290

    Default Re: Help!! Vba code correction

    kajeet,

    I assume that worksheets Sheet1 and Sheet2 already exist.

    Sample raw data in worksheet Sheet1:

    Excel 2007
    ABCDE
    1ScheduleDetailsImpactVerificationvalidation
    2a1@oney
    3b2#twoy
    4c3$threen
    5d4%fourn
    6

    Sheet1





    After the fast macro using two arrays in memory in worksheet Sheet2:

    Excel 2007
    ABCD
    1ScheduleDetailsImpactVerification
    2a1@one
    3b2#two
    4

    Sheet2





    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    Code:
    Option Explicit
    Sub ValidateY()
    ' hiker95, 06/13/2013
    ' http://www.mrexcel.com/forum/excel-questions/708319-help-visual-basic-applications-code-correction.html
    Dim a As Variant, b As Variant
    Dim i As Long, ii As Long, c As Long, n As Long
    Application.ScreenUpdating = False
    With Sheets("Sheet1")
      n = Application.CountIf(.Columns(5), "y")
      If n = 0 Then
        MsgBox "There are no 'y' validations in column E - macro terminated!!!"
        Exit Sub
      End If
      a = .Cells(1).CurrentRegion
      ReDim b(1 To n + 1, 1 To 4)
    End With
    ii = 1
    For c = 1 To 4
      b(ii, c) = a(1, c)
    Next c
    For i = 2 To UBound(a, 1)
      If a(i, 5) = "y" Then
        ii = ii + 1
        For c = 1 To 4
          b(ii, c) = a(i, c)
        Next c
      End If
    Next i
    With Sheets("Sheet2")
      .UsedRange.ClearContents
      .Cells(1).Resize(UBound(b, 1), UBound(b, 2)) = b
      .Columns.AutoFit
      .Activate
    End With
    Application.ScreenUpdating = True
    End Sub
    Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

    Then run the ValidateY macro.
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com