VBA Find

[Puppies72]

Hi all,

I've been rooting aorund trying to find some code that will allow me to look at a value in sheet A and find it in sheet B.

I have found the code below (not mine) but am not sure how to modify it so that instead of finding "Cat" it finds the value in the current active cell in sheet A.

I want to be able to start at 'Validator' BD7 and find that value in 'Data' column A, at the row where that is found I need to copy/paste values from 'Validator' BD:BZ and paste them to (row where the value was found) columns A to W. Once it has done that it needs to loop back to 'Validator' BD8 and continue until it finds an empty cell in 'Validator' BD


Sub Find_Bold_Cat()Dim lCount As LongDim rFoundCell As Range Set rFoundCell = Range("A1") For lCount = 1 To WorksheetFunction.CountIf(Columns(1), "Cat") Set rFoundCell = Columns(1).Find(What:="Cat", After:=rFoundCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) With rFoundCell .ClearComments .AddComment Text:="Cat lives here" End With Next lCountEnd Sub</PRE>

</PRE>

Really stuck on this one
</PRE>

 

[stnkynts]

Enjoy

Sub FindCopyPaste()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Validator")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Data")
Dim ws1lastrow As Long, ws2lastrow As Long
Dim iFind As Range, icell As Range
 
ws1lastrow = ws1.Range("BD" & Rows.Count).End(xlUp).Row
ws2lastrow = ws2.Range("A" & Rows.Count).End(xlUp).Row
 
For Each icell In ws1.Range("BD7:BD" & ws1lastrow)
    Set iFind = ws2.Range("A1:A" & ws2lastrow).Find(What:=icell, After:=ws2.Range("A1"), LookIn:=xlValues, Lookat:=xlWhole)
    icell.Resize(1, 23).Copy Destination:=iFind
Next icell
End Sub

 

[Puppies72]

Thank you so much - will fit this in later this evening and test - will post to let you know how I get on and once again thank you so much - I can already feel my headache going away

 

[stnkynts]

As I thought about it a little bit more I want to make a small amendment to my previous code post. An error could occur if one of the cells in worksheet Validator Range BD7 to the last row in BD is blank. I have inserted code to skip any blank cells in that range just in case.

Option Explicit
Sub FindCopyPaste()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Validator")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Data")
Dim ws1lastrow As Long, ws2lastrow As Long
Dim iFind As Range, icell As Range
 
ws1lastrow = ws1.Range("BD" & Rows.Count).End(xlUp).Row
ws2lastrow = ws2.Range("A" & Rows.Count).End(xlUp).Row
 
For Each icell In ws1.Range("BD7:BD" & ws1lastrow)
    If Not IsEmpty(icell) Then
        Set iFind = ws2.Range("A1:A" & ws2lastrow).Find(What:=icell, After:=ws2.Range("A1"), LookIn:=xlValues, Lookat:=xlWhole)
        icell.Resize(1, 23).Copy Destination:=iFind
    End If
Next icell
End Sub

 

[Puppies72]

Firstly - thank you so much stynkynts!

Your code works a treat
.
.
.
but
.
.
.
how do I amend it so that it copies and pastes the values only from 'Validator' to 'Data' leaving the format in 'Data' unchanged?

Sub ExportMacrotest()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Validator")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Data")
Dim ws1lastrow As Long, ws2lastrow As Long
Dim iFind As Range, icell As Range
 
ws1lastrow = ws1.Range("BD" & Rows.Count).End(xlUp).Row
ws2lastrow = ws2.Range("A" & Rows.Count).End(xlUp).Row
 
For Each icell In ws1.Range("BD7:BD" & ws1lastrow)
    If Not IsEmpty(icell) Then
        Set iFind = ws2.Range("A1:A" & ws2lastrow).Find(What:=icell, After:=ws2.Range("A1"), LookIn:=xlValues, Lookat:=xlWhole)
        icell.Resize(1, 23).Copy Destination:=iFind
    End If
Next icell
End Sub

 

[stnkynts]

Like this:

Sub ExportMacrotest()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Validator")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Data")
Dim ws1lastrow As Long, ws2lastrow As Long
Dim iFind As Range, icell As Range
 
ws1lastrow = ws1.Range("BD" & Rows.Count).End(xlUp).Row
ws2lastrow = ws2.Range("A" & Rows.Count).End(xlUp).Row
 
For Each icell In ws1.Range("BD7:BD" & ws1lastrow)
    If Not IsEmpty(icell) Then
        Set iFind = ws2.Range("A1:A" & ws2lastrow).Find(What:=icell, After:=ws2.Range("A1"), LookIn:=xlValues, Lookat:=xlWhole)
        icell.Resize(1, 23).Copy
        iFind.PasteSpecial xlPasteValues
    End If
Next icell
Application.CutCopyMode = False
End Sub

Two changes were made. One removing the direct destination which is essentially a paste all to a pastespecial paste values only. Second was to add the Application.CutCopyMode = False to clear the copy clipboard when done.

 

[Puppies72]

Great stuff thank you.

Working through this myself to get a better understanding of it am I correct in assuming that the line

icell.Resize(1, 23).Copy

Is the line that selects the number of cells to copy and paste? In this case starting at 1 and then going right 23?

 

[stnkynts]

The resize property changes the range of the specific cell. In this case the defined "icell". It is based on this Resize(Row size, Column Size). So for example if you were to have it as icell.Resize(1,1) it would still be just one cell. icell.Resize(0,0) would be nothing. icell.Resize(1,23) would be one row and 23 columns. Make sense?

 

[Puppies72]

yes indeed - brilliant thank you - if there's one thing better than getting a problem solved is learning how to do it yourself next time - once again ty so much - have lots of wee finicky copy pastes etc that this can help with and its so much faster this way