Select a cell based on column and Row search

**Blacksok** · 03-30-2023, 06:16 PM

Hi All,

Need a hand finding the correct way to get this vba code to work

I'm not using tables for a variety of reason.

I want to select a Person and a type of fruit on Sheet 2
If that person has that fruit listed, It should be removed from their Row.

Im trying to select the row based on (Name) on Sheet1
Then i want to search that row for the fruit name based on Sheet2 (Fruit)
Then clear the cell.

Ive already set the variables

Bit stuck with my very limited vba knowledge.

Thanks guys

Dan


Dim Frng As Range, Qty$, Fruit$, Names$
With Sheets("Sheet2")
Qty = 1: Fruit = .Range("D14"): Names = .Range("C14")

Set Frng = Sheets("sheet1").Range("B:B").Find(Name)
 Row Frng.Select
 Range("Frng:Frng").Find (Fruit)
 If Not Frng Is Nothing Then
 Selection.ClearContents
 End If

**TMS** · 03-30-2023, 07:57 PM

No error checking but this works:

Option Explicit

Sub Test()

Dim Frng As Range, Qty$, Fruit$, Name$
With Sheets("Sheet2")
    Qty = 1: Fruit = .Range("D14"): Name = .Range("C14")
    
    Set Frng = Sheets("sheet1").Range("B:B").Find(Name)
    Set Frng = Frng.Resize(, 8).Find(Fruit)
    If Not Frng Is Nothing Then
        Frng.ClearContents
    End If
End With

End Sub

First problem was Dimming Names$ and then trying to find Name. Use Option Explicit. Then some Range references were wrong.

Select a cell based on column and Row search

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Select a cell based on column and Row search

Re: Select a cell based on column and Row search

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] select entire column based on activecell - multiply cells based if found empty cell

How do I select a row based on a search in a column?

Select cell based on the last entry in other column

[SOLVED] Select all data in a column based on search return

select column based on cell input

Search column and select the cell

Select a column based on a cell entry

Bookmarks

Bookmarks

Posting Permissions