• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

FIND VALUES AND PASTE OFFSET

K

kingcashandcarry

Member
in a range n2:p17000 there are cells with repeatable texts about 400 times
winbank
winbank2
winbank3
cash
cash1
cash2

i am trying to write a code that will search in the range and select all the cells with WINBANK and cash the exact value (not winbank2 or winbank3)
but offset 7 columns right to T COLUMN so to paste to that cells a copied cell a1

first i saw that i can't record macro with find
 

Attachments

Try This Macro
Code: 
Option Explicit
         
Sub filter_for_ME()
    With Application
     .ScreenUpdating = False
     .Calculation = xlCalculationManual
    End With
Dim S_sh As Worksheet: Set S_sh = Sheets("Sheet1")
Dim k As Long, m As Long
Dim lrN As Long: lrN = Cells(Rows.Count, "N").End(3).Row
S_sh.Range("a1").CurrentRegion.ClearContents
k = 1: m = 1
 Do Until k > lrN
   If Cells(k, "N") = "WINBANK" Or _
     Cells(k, "N") = "CASH" Then _
     Cells(m, 1) = "OK": m = m + 1
     k = k + 1
   Loop
    With Application
     .ScreenUpdating = True
     .Calculation = xlCalculationAutomatic
    End With
End Sub
 

Attachments

First of all thanks to the community for the help that save me a lot of times
second a big sorry for not responding but i was off and i didn't have access to the office and accounts
now about your ideas i tested but 2 things
i tried too to filter cells but because my sheet has many formattings and merged cells can't do it.so the solution with filter it isn't possible i think
second maybe i wasn't clear but i want the value in a1 to be copied and paste to selected cells of T columns based on value of WINBANK and CASH in N column

sorry my English is poor too
 
i rearrange the merged cells and I add this to the line to paste the formula of the cell a1
.Range("T2:T" & n_max) = .Range("A1").Formula
for another project that i want to use it

the thread is closed
have a nice day
 
You must log in or register to reply here.
Back
Top