Results 1 to 5 of 5

Thread: find next and loop combined

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Newbie
    Joined
    Oct 2009
    Posts
    2
    Location

    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
  •