Solved

VBA Excel

Posted on 2013-11-19
4
275 Views
Last Modified: 2013-11-20
Could someone please give us some help with this code...

1) Read DRA Summary sheet and find GQ-000

2) Find GQ-000 in the Data sheet

3) Get the value in the 5th column(Data sheet) and move it to the cell that is above the cell where GQ-000 was found in the DRA Summary sheet.

4) Repeat until all occurrences of GQ-* have been found.
I attached the workbook also.

Code so far...

Sub Get_GQnumber1()

    Dim rng As Range, cell As Range
    Dim lc As Long, ResultCol As Long
    Dim s As String, sInput As String

    lc = Cells(3, Columns.Count).End(xlToLeft).Column

         ResultCol = 5

    Set rng = Range(Cells(3, 1), Cells(3, lc))

    'Set lookup range

    Set LkupRng = Sheets("Data").Range("A1").CurrentRegion

    For Each cell In rng
        If cell.Value Like "GQ-*" Then
            'MsgBox cell.Value
            s = Left(cell.Value, InStr(1, cell.Value & " ", " ") - 1)
            'MsgBox "s: " & s

            On Error Resume Next
            v = Application.VLookup(s, Worksheets("Data").Range("lc"), 5, 0)
                     
            x = Application.VLookup(s, Worksheets("Data").Range("A:E"), 5, 0)

           Sheets("DRA Summary").Range("F2") = v

            End
      End If

    Next

End Sub
Data.xlsm
0
Comment
Question by:tesla764
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 34

Expert Comment

by:Norie
ID: 39663172
Remove this, it stops all code execution.
End

Open in new window

0
 
LVL 6

Expert Comment

by:Michael
ID: 39663407
Nevermind my question, I misread...
0
 
LVL 6

Accepted Solution

by:
Michael earned 500 total points
ID: 39663465
Try this:

Sub Get_GQnumber1()

    Dim rng As Range, cell As Range
    Dim lc As Long, ResultCol As Long
    Dim s As String, sInput As String

    lc = Cells(3, Columns.Count).End(xlToLeft).Column

    ResultCol = 5

    Set rng = Range(Cells(3, 1), Cells(3, lc))

    'Set lookup range
    Set LkupRng = Sheets("Data").Range("A1").CurrentRegion

    For Each cell In rng
        If cell.Value Like "GQ-*" Then
            'MsgBox cell.Value
            s = Left(cell.Value, InStr(1, cell.Value & " ", " ") - 1)
            'MsgBox "s: " & s

            On Error Resume Next
            v = Application.VLookup(s, LkupRng, ResultCol, 0)
            
            cell.Offset(-1).Value = v
        End If

    Next

End Sub

Open in new window

0
 

Author Closing Comment

by:tesla764
ID: 39663512
Thanks everything is looking good.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question