Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to select a column of data from a loop

Posted on 2011-09-19
9
Medium Priority
?
257 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 35

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 35

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 35

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 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

636 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