If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Using VBA to find a value and select a range
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" wrote in message ... 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? |
#3
|
|||
|
|||
Using VBA to find a value and select a range
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" skrev i meddelelsen ... 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? |
#4
|
|||
|
|||
Using VBA to find a value and select a range
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" wrote in message ... 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" wrote in message ... 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? |
#5
|
|||
|
|||
Using VBA to find a value and select a range
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" wrote in message ... 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" wrote in message ... 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? . |
Thread Tools | |
Display Modes | |
|
|