Using VBA to find a value and select a range

[AlexJarvis]

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?

 

[Rick Rothstein]

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

 

[Per Jessen]

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

 

[Rick Rothstein]

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)

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)

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?

 

[AlexJarvis]

This one is right on the money. Thank you!

-A

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)

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)

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?

.