Using VBA to find a value and select a range

I am trying to write a macro that will let me find a specific vale in a 
specific column, then select part of the row that the value is in, beginnign 
with a column 31 cells away from the column that the specific value is in.  
Someone suggested to me that I use an IF statement, but this is all I know 
about that:

IF [value in column B] is “Grand Total”  Then select from column AF to the 
end of the array in that row and copy it and paste a transpose of the values 
starting in cell I9 of worksheet X.

What is the best way for me to accomplish this goal?
0
Utf
4/27/2010 4:19:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
2271 Views

Similar Articles

[PageSpeed] 10

Does this macro do what you want?

Sub FindGrandTotal()
  Dim StartDataColumn As Long, LastDataColumn As Long
  StartDataColumn = 31
  LastDataColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                   SearchDirection:=xlPrevious, LookIn:=xlValues).Column
  Columns("B").Find("Grand Total", LookAt:=xlWhole, MatchCase:=False). _
                    Offset(0, StartDataColumn - 1).Resize(1, _
                    LastDataColumn - StartDataColumn).Select
End Sub

-- 
Rick (MVP - Excel)



"AlexJarvis" <AlexJarvis@discussions.microsoft.com> wrote in message 
news:B4C6671F-A13E-4904-AF53-9F70833846B2@microsoft.com...
> I am trying to write a macro that will let me find a specific vale in a
> specific column, then select part of the row that the value is in, 
> beginnign
> with a column 31 cells away from the column that the specific value is in.
> Someone suggested to me that I use an IF statement, but this is all I know
> about that:
>
> IF [value in column B] is “Grand Total”  Then select from column AF to the
> end of the array in that row and copy it and paste a transpose of the 
> values
> starting in cell I9 of worksheet X.
>
> What is the best way for me to accomplish this goal? 

0
Rick
4/27/2010 4:52:20 PM
Hi

Try this:

Sub test()
Dim fFound As Range
Dim f As Variant
Dim SearchRng As Range
Dim DestRng As Range

Set DestRng = Worksheets("Sheet X").Range("I9") ' Change Sheet name
Set SearchRng = Range("B1", Range("B" & Rows.Count).End(xlUp))
Set f = Cells.Find(What:="Grand Total", after:=Range("B1"), _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If f Is Nothing Then Exit Sub 'No match found

Set fFound = f
Do
    fRow = f.Row
    Range("AF" & fRow, Range("AF" & fRow).End(xlToRight)).Copy
    DestRng.PasteSpecial xlPasteAll, Transpose:=True
    Set DestRng = DestRng.Offset(0, 1) ' next match is pasted in J9
    SearchRng.FindNext , after:=f
Loop Until f.Address = fFound.Address
End Sub

Regards,
Per

"AlexJarvis" <AlexJarvis@discussions.microsoft.com> skrev i meddelelsen 
news:B4C6671F-A13E-4904-AF53-9F70833846B2@microsoft.com...
> I am trying to write a macro that will let me find a specific vale in a
> specific column, then select part of the row that the value is in, 
> beginnign
> with a column 31 cells away from the column that the specific value is in.
> Someone suggested to me that I use an IF statement, but this is all I know
> about that:
>
> IF [value in column B] is “Grand Total”  Then select from column AF to the
> end of the array in that row and copy it and paste a transpose of the 
> values
> starting in cell I9 of worksheet X.
>
> What is the best way for me to accomplish this goal? 

0
Per
4/27/2010 4:58:54 PM
Sorry, I misread your question. Give this macro a try instead...

Sub FindGrandTotal()
  Dim GrandTotal As Range, DataSheet As Worksheet, CopySheet As Worksheet
  Set DataSheet = Worksheets("Sheet3")
  Set CopySheet = Worksheets("X")
  Set GrandTotal = DataSheet.Columns("B").Find("Grand Total", _
                           LookAt:=xlWhole, MatchCase:=False)
  If Not GrandTotal Is Nothing Then
    CopySheet.Range("I9").Resize(Columns.Count - 31) = WorksheetFunction. _
              Transpose(Range(GrandTotal.Offset(0, 30), DataSheet.Cells( _
              GrandTotal.Row, Columns.Count)))
  End If
End Sub

-- 
Rick (MVP - Excel)



"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
news:OJCPgoi5KHA.5016@TK2MSFTNGP02.phx.gbl...
> Does this macro do what you want?
>
> Sub FindGrandTotal()
>  Dim StartDataColumn As Long, LastDataColumn As Long
>  StartDataColumn = 31
>  LastDataColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
>                   SearchDirection:=xlPrevious, LookIn:=xlValues).Column
>  Columns("B").Find("Grand Total", LookAt:=xlWhole, MatchCase:=False). _
>                    Offset(0, StartDataColumn - 1).Resize(1, _
>                    LastDataColumn - StartDataColumn).Select
> End Sub
>
> -- 
> Rick (MVP - Excel)
>
>
>
> "AlexJarvis" <AlexJarvis@discussions.microsoft.com> wrote in message 
> news:B4C6671F-A13E-4904-AF53-9F70833846B2@microsoft.com...
>> I am trying to write a macro that will let me find a specific vale in a
>> specific column, then select part of the row that the value is in, 
>> beginnign
>> with a column 31 cells away from the column that the specific value is 
>> in.
>> Someone suggested to me that I use an IF statement, but this is all I 
>> know
>> about that:
>>
>> IF [value in column B] is “Grand Total”  Then select from column AF to 
>> the
>> end of the array in that row and copy it and paste a transpose of the 
>> values
>> starting in cell I9 of worksheet X.
>>
>> What is the best way for me to accomplish this goal?
> 
0
Rick
4/27/2010 5:24:53 PM
This one is right on the money.  Thank you!  

-A

"Rick Rothstein" wrote:

> Sorry, I misread your question. Give this macro a try instead...
> 
> Sub FindGrandTotal()
>   Dim GrandTotal As Range, DataSheet As Worksheet, CopySheet As Worksheet
>   Set DataSheet = Worksheets("Sheet3")
>   Set CopySheet = Worksheets("X")
>   Set GrandTotal = DataSheet.Columns("B").Find("Grand Total", _
>                            LookAt:=xlWhole, MatchCase:=False)
>   If Not GrandTotal Is Nothing Then
>     CopySheet.Range("I9").Resize(Columns.Count - 31) = WorksheetFunction. _
>               Transpose(Range(GrandTotal.Offset(0, 30), DataSheet.Cells( _
>               GrandTotal.Row, Columns.Count)))
>   End If
> End Sub
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> 
> "Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
> news:OJCPgoi5KHA.5016@TK2MSFTNGP02.phx.gbl...
> > Does this macro do what you want?
> >
> > Sub FindGrandTotal()
> >  Dim StartDataColumn As Long, LastDataColumn As Long
> >  StartDataColumn = 31
> >  LastDataColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
> >                   SearchDirection:=xlPrevious, LookIn:=xlValues).Column
> >  Columns("B").Find("Grand Total", LookAt:=xlWhole, MatchCase:=False). _
> >                    Offset(0, StartDataColumn - 1).Resize(1, _
> >                    LastDataColumn - StartDataColumn).Select
> > End Sub
> >
> > -- 
> > Rick (MVP - Excel)
> >
> >
> >
> > "AlexJarvis" <AlexJarvis@discussions.microsoft.com> wrote in message 
> > news:B4C6671F-A13E-4904-AF53-9F70833846B2@microsoft.com...
> >> I am trying to write a macro that will let me find a specific vale in a
> >> specific column, then select part of the row that the value is in, 
> >> beginnign
> >> with a column 31 cells away from the column that the specific value is 
> >> in.
> >> Someone suggested to me that I use an IF statement, but this is all I 
> >> know
> >> about that:
> >>
> >> IF [value in column B] is “Grand Total”  Then select from column AF to 
> >> the
> >> end of the array in that row and copy it and paste a transpose of the 
> >> values
> >> starting in cell I9 of worksheet X.
> >>
> >> What is the best way for me to accomplish this goal?
> > 
> .
> 
0
Utf
4/27/2010 7:38:01 PM
Reply:

Similar Artilces:

Conditional Comment using Cell value as part of it
What I'm trying to do is the following: if cell(a1) is different from 0(zero) then, cell(b1) should have a comment that say, the value of cell(c1) is the payment, next line the value of cell(a1) was refund, next line Total for today = cell(c1)-cell(a1) Example a1 = $10.00 c1 = $30.00 B1 (comment): $30.00 is the payment (c1) $10.00 was refund (a1) Total for today: $20.00 (c1-a1) can anyone help me on that? thanks in advance ...

select sheets for printing
Can anyone tell me how to select sheets in a workbook based on a cell value for printing? I have many sheets, I want to select all the sheets that have a value greater than 0 in C2 for printing, which I guess will then go something like: ActiveWindow.SelectedSheets.PrintOut Thanks in advance for help Benny bennyob, try this, Sub Print_sheets() 'will print sheet with a vavle >0 in C2 For Each Sheet In ThisWorkbook.Worksheets Sheet.Activate If Range("C2").Value > 0 Then 'use this to test and comment out below ActiveSheet.PrintPreview 'use thi...

Why is .NET 2.0 required to deploy an app using VC2005 C++ Setup project?
I just created a setup project for my mfc app. I noticed that in the prerequisites section the .NET 2.0 is checked by default. My app does not need the framework (at least this is what I think). If I clear the checkbox, build the project, and then try to deploy it on a machine with ..NET 1.1 the installer fails with a message complaining about missing .NET 2.0. I'm not sure if this the installer that needs the framework or my app. Are there any mfc8 dlls dependant on the framework? Thanks Hi, > I just created a setup project for my mfc app. I noticed that in the > p...

Change font size based on value of a cell
If the value of A1>0, I need the font size in a merged cell to change from the default 10 to 16. It needs to return to the default size when A1 returns to a value of 0. Can someone help with this? Thanks. Michael Here's a little macro that will do it....... Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Range("a1").Value = 0 Then Range("B1:E3").Font.Size = 10 Else Range("B1:E3").Font.Size = 16 End If Range("B1").Select End Sub Vaya con Dios, Chuck, CABGx3 "MichaelRLanier@gmail.com" wrote: > ...

How do I print a select number of address records from Access
I'm trying to print just a select number of records for a mailing that I'm doing. I can't seem to figure out how to do this. Thanks. On Thu, 8 Nov 2007 12:23:04 -0800, robbie6 <robbie6@discussions.microsoft.com> wrote: >I'm trying to print just a select number of records for a mailing that I'm >doing. I can't seem to figure out how to do this. Thanks. Create a Query based on the table containing the addresses (and perhaps other tables). View the query's Properties (with the View menu item, or rightclick the background behind the table icons and sele...

Chart Value Changes in VBA
When you have say a bar chart and drag the bar downwards this will change the value in the reference source cell. Is there a way through VBA to identify the source and the cell changed by when dragging the bar. That is, when you drag the bar in the chart let me know the cell that ahs changed! Any help would be appreciated. Thanks in advance. Howard John, If your objective is to prevent the cell changes via the chart, you can protect the worksheet (Tools - Protection). The cells should be locked (Format - Cells - Protection). Or you can use the Chart_SeriesChange event macro for the cha...

Calculation time
I have a large file, approx 10MB, in excel 2003. Most of the data relates to calculations for around 5,000 records, and the workbook takes too long to recalculate. All the vlookup's have been replaced with Index / Match, but it's still slow, and I have a number of macros that rely upon it recalculating. Based on a post here, I went to this site: http://msdn.microsoft.com/en-us/library/aa730921.aspx and ran the timer macros to try and isolate the problem. The FullCalcTimer takes around 19 secs The ReCalcTimer takes 0.00013 secs, and The SheetTimer takes no more than 0.01...

Setting AutoNumber to Non-Default Value
I would like to use the AutoNumber data type within a field. However I would like the sequential autonumbering to begin from 6000 rather than the default value of 1(for the first record). Thanks for your help in advance. Jeff I suggest you may not want to use Autonumber for that use. Autonumbers are designed to provide unique numbers. It in not designed to provide numbers in order and for a number of reasons may not do so. As a result using them in any application where the user sees the numbers is likely to end up with confusion. There are other ways of providing the numbers...

How to use Find (Ctrl-F) to find non-alpha
Is there some way to search a sheet for anything BUT a-z or A-Z? tx ;-) Finding numerals or a mixture of numbers and letters (and other characters) works ok for me. What are you having trouble with? On 10/22/2010 16:31, Heather Mills wrote: > Is there some way to search a sheet for anything BUT a-z or A-Z? > > tx ;-) -- Dave Peterson On Fri, 22 Oct 2010 17:54:28 -0500, Dave Peterson <petersod@XSPAMverizon.net> wrote: >Finding numerals or a mixture of numbers and letters (and other characters) >works ok for me. > >What are you having trouble with? > >...

Advanced find, and the grouping and ungrouping of clauses
One of my users has come with this replicable issue: 1. Put in a number of line items in the Advanced Find, select the rows, and click "Group OR" 2. Run it - - it works fine. 3. Go back, ungroup the selection. 4. Add another line item, select rows, and click on the "Group OR" What happens: 1. The "OR" clause doesn't appear 2. The first line item in the Advanced Find disappears 3. There is a message of "Error on page" If you attempt to click "Group OR" again, the NEXT line item in the Advanced Find disappears. Has anyone else experience...

Counting no of days of a specific range of days from a list
I have a date range from month first to end of month. I want to count no of entries from that list which range from 10th to 15th. For eg. a list starting from 01.11.2009 to 30.11.2009. I want to count no of entries of date range from 15.11.2009 to 20.11.2009. Please help me.... Assume that you are having the Dates in A Column and do you want to get the number of days between 15.11.2009 to 20.11.2009. =VALUE(TEXT(DAY(COUNTIF(A:A,">"&DATE(2009,11,14))-COUNTIF(A:A,">"&DATE(2009,11,20))),"#########")) In your example the dates are entere...

Advanced Find Question 11-13-07
Is there any way to remove entities from the Advanced Find Query / Window? I would like to remove them as possible entities to "search" against. Thanks in advance, Hi, That is not possible. I assume if some user dont have read rights on some entity then it will not appear in Advance Find. -- PLEASE do click on Yes or No button if this post is helpful or not for our feedback. uMar Khan :: Freelance Consultant My Email :: imumar at gmail dot com My Blog :: http://umarkhan.wordpress.com "CPW" wrote: > Is there any way to remove entities from the Advanced Find Que...

VC++ 2005
I am attempting to compile a project in Visual Studio 2005 which compiles and links properly in VC++ 6.0. It seems to have problems with C++ i/o functions. Here is the error I get: g:\src\analyzer\digitdialpages.h(136) : error C2146: syntax error : missing ';' before identifier 'm_InFile' Line 136: ifstream m_InFile; The statment: include <fstream> is at the top of the source file. Some assistance would be helpful. We didn't have any problems with using ifstream or ofstream in VC++ 6.0. Thanks. On Tue, 12 Dec 2006 18:22:47 -0500, "Raj Kulkarni...

How do I create custom border using peoples names?
There are many it could be done. I'd set up text boxes and go from there. -- JoAnn Paules MVP Microsoft [Publisher] "Val" <Val@discussions.microsoft.com> wrote in message news:BFF16BB3-4C90-4E05-B0AA-303874A445AA@microsoft.com... > In addition to JoAnn's reply, you could use WordArt as well. WordArt allows you the opportunity to rotate, flip, arrange etc. and resize as well as save as an image. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no rights. &...

How do I find root mean square of a periodic data in Excel
...

Dynamic range in chart
Hi, I have created a named range called Net_CLI which has the following formula =INDIRECT("Trends!$C$4:$" & VLOOKUP(COUNTA(Trends!$B$4:$V$4),'Legend Sheet'!$B$31:$C$50,2,FALSE) & "$4") Presently I expect the vlookup part of the above formula to return H, so in all I expect the named range to give me ----> Trends!$C$4:$H$4 If I use the above named range in Data Validation, then I get the relevant values in drop down. But If I have a chart (in the same worksheet) in which for the first series in place of existing entry in the values textbox as =Trends...

match dates in two columns get value from third
I have a data set on daily basis and another data set on weekly basis. i want to put the weekly data in daily data set by putting a formula which picks values from col c for relevant dates in daily set. example - let us say this is the data set ColA Col B Col C jan1 jan1 8 jan2 jan7 7 jan3 jan4 jan5 jan6 jan7 I want the data from colC against dates in ColA as in ColD below. it should leave other cells blank (colA and ColB are dates format) ColA Col B Col C ColD jan1 jan1 ...

deleting entire rows with the same cell value in the first column
Hi! I have an huge table in which there are lots of rows that have the same value as the one in the row before: 1 1 1 2 2 2 How can I delete them leaving this way only one row per value without searching for them and deleting them manualy? Any help welcome! Pedro, assuming your data starts in a1, in b1 type =if(a1=a2,"Y","") and copy down for the length of your data. Then filter on Y and delete all those rows. HTH -- Sincerely, Michael Colvin "Pedro F." wrote: > Hi! > I have an huge table in which there are lots of rows that have the same >...

want to find duplicate Phone numbers when typed in excel 2003
I create a column and when i put a phone number in I want to have it indivcate if its a duplicate perferrable in red Let's assume this is your data: A1: 12345 A2: 54321 A3: 12345 Which cell(s) should be highlighted? -- Biff Microsoft Excel MVP "trvlpal" <trvlpal@discussions.microsoft.com> wrote in message news:4C707764-79F0-4ECB-BF57-661289BB59D6@microsoft.com... >I create a column and when i put a phone number in I want to have it > indivcate if its a duplicate perferrable in red Using column "A", place this formula in "conditional formatt...

DLookup default Value
I am having trouble getting the correct syntax using DLookup as a default value in a text box on a form. Form Name: FrmMainInput Unbound control Name: FunctionNumber I am trying to get the default value to look up the FunctionNumber, using the FunctionName from the table TblFunction. I am using the expression below in the default value of the FunctionNumber control, but am not getting any value. =DLookUp("[FunctionNumber] ","TblFunction"," [FunctionName] =" & [Forms]![FrmMainInput]![FunctionNumber] & "") "rbb101" <rbb...

Future Value function with differenct cash flows
Excel does not seem to have a FV function or variant that allows you to compute the FV of different cash flows at regular periodic intervals given a constant rate. Is there such a function? I can think of several applications where the FV of CF's are needed wherein CF's are at both even and uneven intervals or said another way, at set periods versus random periods and at different CF amounts. There are several work around solutions I can think of, but they tend to be cumbersome. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggest...

how to remove non commercial use in the title bar in office
how to remove non commercial use in the title bar in office 2007 Buy an edition that is not for non-commercial use only. You own the Home and Student edition. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Mario" <Mario@discussions.microsoft.com> wrote in message news:0E14F850-0B65-4093-926F-B680E1C7D6B1@microsoft.com... > how to remove non commercial use in the title bar in office 2007 ...

counting values in a column in Access
I am working on a database for help desk type calls. I have three call categories and would like a running total of how many of each call I recieve. These three values are located in one column of a table. What are my options to make this happen? Example: Date Call Type Problem 11/5 Equipment VCR doesn't work 11/6 Computer Monitor wont turn on 11/6 Media CD is scratched 11/7 Equipment TV has no volume I would like a report or something telling me that I have 2 Equipment calls 1 Computer Call 1 Media Call Use a Totals query. If build...

vlookup cant be used
I need a way to transfer data. I have one sheet that contains all information. All of this data needs to be transferred to a template ready for print. I have contemplated using a vlookup to quicken the method, although the data sheet will be moving too and from computers, and so the vlookup will not work. Is there any code that might possibly help me, i.e. a code that looks for the datasheet and does some sort of vlookup after it has located the file? Or should this problem be tackled using macros, although i imagine that this will have the same effect. How "ready for print"? Are...

Passing Cell values to a SQL statement
I have set up a spreadsheet that uses excel's "Get external data" feature to connect to a SQL database and run a querry. The problem is that I need to get data for several different product codes. These codes change from time to time but basically the querry looks like this Select * from SalesData Where SalesData.productCode = 1 Or SalesData.productCode = 2 is there a way to set it up such that the product codes are passed to the statement from cell values in a given range? This way it would dynamically retrive whatever I am looking for. Also if any of you double as SQL wizza...