Solved

How to select a column of data from a loop

Posted on 2011-09-19
9
251 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
[X]
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
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 34

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:Arno Koster
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:Arno Koster
ID: 36560194
Then change the value 5 to the number of cells you want to copy.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 34

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 34

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

739 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