Solved

How to select a column of data from a loop

Posted on 2011-09-19
9
252 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

705 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