Solved

How to select a column of data from a loop

Posted on 2011-09-19
9
248 Views
Last Modified: 2012-05-12
I have a macro that finds a range on a sheet with monthly data. (There are many ranges of monthly data for different usages)
I have included an image of the sample data and the code The code works to find the CurMo (which is Aug) but the active cell is still O2 with Jan
I need to find the CurMo value, Copy the data in the column below that title and paste that data into another open woorkbook.

Any help and direction is greatly appreciated

sampledata
' Find Monthly plan range titled with "Planned Dollars
    Cells.Find(What:="Planned Dollars", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

' Move to cell in Row with Month title
    ActiveCell.Offset(1, 0).Select
    
'***Need to select Currrent Cell and 11 more on right
Range(ActiveCell, "Z2").Select

        For Each Cell In Selection
            If Cell.Value = CurMo Then
            '** I need to select the column that has the matched value***************
            '** This currently finds a match to Aug but the active cell is still O2
            '**Does not work "Range(ActiveCell).Select"
            
                Range(ActiveCell, ActiveCell.End(xlDown)).Select
                Exit For
            End If
        Next Cell
' Copy Values in column as Current Moth Planned  dollars
    Selection.Copy
    Windows("GTFPTemplate.xls").Activate

    Range("T7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Windows("2011 U.S. Financial Plan.xls").Activate

Open in new window

0
Comment
Question by:Mswetsky
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 36560098
Could you attach a workbook with the data?

The image can be copied but I've yet to come across any OCR software that would convert something like that to usable data.:)

PS If you didn't use all the Select/Activate then you probably wouldn't have this problem. I might have mentioned that elsewhere.
0
 
LVL 19

Expert Comment

by:akoster
ID: 36560190
you could use something like
    Set result = UsedRange.Rows(2).Find(what:=CurMo, lookat:=xlWhole)
    If result Is Nothing Then
        MsgBox "Current month " & CurMo  & " not found !"
        Exit Sub
    Else
         Range(result.Address & ":" & result.Offset(5, 0).Address).Copy Workbooks("GTFPTemplate.xls").Worksheets("Sheet1").Range("T7")
    End If

Open in new window

0
 
LVL 19

Expert Comment

by:akoster
ID: 36560194
Then change the value 5 to the number of cells you want to copy.
0
ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36560214
In simplest terms, you would change this:

Range(ActiveCell, ActiveCell.End(xlDown)).Select

to this:

Range(Cell, Cell.End(xlDown)).Select

Regards,
Rory
0
 
LVL 33

Expert Comment

by:Norie
ID: 36560248
Try this.
Dim rngFnd As Range
Dim rngCurMonth As Range

    CurMo = "Aug"
    
    ' Find Monthly plan range titled with "Planned Dollars

    Set rngFnd = Range("O:O").Find(What:="Planned Dollars", After:=Range("O1"), LookIn:=xlFormulas _
                                                                                        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                   MatchCase:=False, SearchFormat:=False)

    Set rngCurMonth = rngFnd.Offset(1).Resize(, 12).Find(What:=CurMo, SearchOrder:=xlNext)
    
    Set rngCurMonth = Range(rngCurMonth, Cells(Rows.Count, rngCurMonth.Column).End(xlUp))

    rngCurMonth.Copy

    Workbooks("GTFPTemplate.xls").ActiveSheet.Range("T7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                                                                :=False, Transpose:=False

Open in new window

0
 
LVL 1

Author Comment

by:Mswetsky
ID: 36561516
imnorie, Thanks again for your suggestions.
I use all the Select/Activate because I usually record a macro to do what I want, and then I try to make the recorded cell movements dynamic.

Rory, This is almost GREAT
 How can I move down 1 row then use your suggestion.
I don't want to copy the title row, just the data below it.
                Range(Cell).Select '*****doesn't work
                ActiveCell.Offset(1, 0).Select
                Range(Cell, Cell.End(xlDown)).Select


0
 
LVL 33

Expert Comment

by:Norie
ID: 36561573
Mswestsky

I know recorded macros have select/activate etc but that doesn't make necessarily make them dynamic.

Did you try the code I posted?

It works.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 36561765
For Each Cell In Selection
            If Cell.Value = CurMo Then
               Set cell = cell.offset(1)
                Range(Cell, Cell.End(xlDown)).Select
                Exit For

Open in new window

0
 
LVL 1

Author Closing Comment

by:Mswetsky
ID: 36561798
Thank you very much for the simple solution, and in a way I understand
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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