Hello!
So I have this table for example. I want for it to create a new sheet with the vegetable name. Like this: search the price column for value= 10, find the corresponding vegetable for that price, which will be the red tomatoes and copy the words "red tomatoes" and create a new sheet which will be named "red tomatoes".
But I don't want to use ranges like B1 or E2, I want it to works no matter how many other random columns I insert between the vegetables and the price.
So: To find the 10 value under the column of Price, using the header("Price") of the column not column E, to know how to follow the same row until it reaches the column "Vegetable" not column B, and copy the value.
I don't know if it can be done or not.
I thought about something like this, but maybe you have a simpler way. And if I have 2 values of 10 in the price column, I need a loop to do the exact same thing for every 10 values.
Thanks.
So I have this table for example. I want for it to create a new sheet with the vegetable name. Like this: search the price column for value= 10, find the corresponding vegetable for that price, which will be the red tomatoes and copy the words "red tomatoes" and create a new sheet which will be named "red tomatoes".
But I don't want to use ranges like B1 or E2, I want it to works no matter how many other random columns I insert between the vegetables and the price.
So: To find the 10 value under the column of Price, using the header("Price") of the column not column E, to know how to follow the same row until it reaches the column "Vegetable" not column B, and copy the value.
I don't know if it can be done or not.
I thought about something like this, but maybe you have a simpler way. And if I have 2 values of 10 in the price column, I need a loop to do the exact same thing for every 10 values.
Code:
Public Sub findColumn_Select()
Dim coll As Range, cl as Range
Dim address As String
Dim selrow As Integer, selcol As Integer
Set coll = Range("A1:Z100").Find("price")
If coll Is Nothing Then
MsgBox "Description column was not found."
Exit Sub
End If
Range(coll, coll.End(xlDown)).Select
Selection.Find(What:="10", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
address = ActiveCell.address
Range(address).Select
selrow = Selection.row
With Worksheets("Sheet1").Cells
Set cl = .Find("Vegetables", After:=.Range("A2"), LookIn:=xlValues)
If Not cl Is Nothing Then
cl.Select
End If
End With
selcol = Selection.Column
Cells(selrow, selcol).Select
End Sub
Thanks.
Last edited by a moderator: