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
Solved

VBA Excel

Posted on 2013-11-19
273 Views
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
Question by:tesla764
• 2

LVL 33

Expert Comment

ID: 39663172
Remove this, it stops all code execution.
``````End
``````
0

LVL 6

Expert Comment

ID: 39663407
0

LVL 6

Accepted Solution

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
``````
0

Author Closing Comment

ID: 39663512
Thanks everything is looking good.
0

Featured Post

Question has a verified solution.

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

For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…