-
find next and loop combined
Here is my coding...this is my first post, so i'm assuming that it's ok to post code here...if not, SORRY !!!
[vba]Sub Macro2()
'
' This searches using the GL as the constant
'
Dim X As String
Sheets("Reports").Select
X = Range("H7").Value
Sheets("Input List").Select
Range("L11").Select
Cells.Find(What:=X, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Activate
ActiveCell.Offset(0, -8).Activate 'QSR NUMBER
Selection.Copy
Sheets("Reports").Select 'COPY/PASTE QSR NUMBER
Range("B18").Select
empty_cell 'run empty cell routine
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Input List").Select
ActiveCell.Offset(0, 3).Activate 'TREND
Selection.Copy
Sheets("Reports").Select 'COPY/PASTE TREND
ActiveCell.Offset(0, 1).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Input List").Select
ActiveCell.Offset(0, 1).Activate 'OCCURENCE
Selection.Copy
Sheets("Reports").Select 'COPY/PASTE OCCURENCE
ActiveCell.Offset(0, 1).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Input List").Select
ActiveCell.Offset(0, 5).Activate 'COST
Selection.Copy
Sheets("Reports").Select 'COPY/PASTE COST
ActiveCell.Offset(0, 1).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Input List").Select
ActiveCell.Offset(0, 1).Activate 'DESCRIPTION
Selection.Copy
Sheets("Reports").Select 'COPY/PASTE DESCRIPTION
ActiveCell.Offset(0, 1).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Do
Do While ActiveCell.Text <> ""
ActiveCell.Offset(1, 0).Select
If ActiveCell.Text = "" Then
ActiveCell.Activate
End If
Loop
Loop Until ActiveCell.Text = ""
End Sub
[/vba] The loop is where i'm getting stuck. What i need the code to do, is search through a sheet (Input List is the name of the sheet), look for the text that is in a certain cell ( H7 of the Input List sheet), copy a certain range of cells, and then pastes it on the second sheet ( Reports is the name of the sheet), (here comes the problem)
go back to the first sheet, start looking at the information again, copy and pasting ONLY the next row of cells that has the same value in H& from the input list sheet. this process will run till it hits the last row for input (row 1162) OR to make it faster, till there is a blank cell with no value in it.
Can ANYONE please help me ?!?!?!?!
Thanks in advance,
Nucor
Last edited by Aussiebear; 10-30-2009 at 02:14 PM.
Reason: Added VBA tags to code
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules