Solved

How to select a column of data from a loop

Posted on 2011-09-19
9
247 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now