this is the code I wrote. The comments below end sub is code that works for what I am trying to do. I get an "Object variable or with block not set" error in line
Sheets(ws).Range("g2") = foundCell.Address [CODE].
Here's everything
[CODE] Sub FindCellAddress() Dim rng As Range Dim foundCell As Range Dim searchValue As Text Dim ws As Worksheet Set ws = Sheets("input") For x = 4 To 13 searchValue = Sheets(ws).Cells(x, 7) MsgBox searchValue Set rng = Worksheets("Sheet1").Range("i1:k20") Set foundCell = rng.Find(What:=searchValue, LookIn:=xlValues) Sheets(ws).Range("g2") = foundCell.Address MsgBox foundCell.Address Next x End Sub '---------------------this works for finding address-------------- ' searchValue = "cinnamon" ' Set rng = Worksheets("Sheet1").Range("i1:k20") ' Set foundCell = rng.Find(What:=searchValue, LookIn:=xlValues) ' Range("g2") = foundCell.Address '---------------------------------
It looks to me like my variable is defined