Find instances of Value VBA

[Noz2k]

What I want to do is on the click of a button, I want to search sheet1 columnBS for all values are equal to a specified criteria.

I then want to populate part of sheet2 with the values in the same row as the first instance where the value is found.

and then if there is a 2nd value I want to populate another part of Sheet2 with the values in the same rows as where the 2nd instance is found.

and then if a 3rd and so on.


Effectivley, all instances are being posted into identical forms, just further down the sheet. So therefore the space between the cells will always be constant (so some sort of offset should work).

The main issue I'm having at the moment is how to search for all instances?

The start of my code is as followed:

Dim WeekStart As Date
WeekStart = Date - (Weekday(Date, 3))
ActiveWorkbook.Sheets("Sheet1").Activate
Sheets("Sheet1").Unprotect "PasswordText"

so I want to search columnBS in Sheet1 for instances where Weekstart is found.

 

[MrKowz]

Something like this?

Public Sub FindAllInstances()
Dim rng         As Range, _
    rng1        As String, _
    WeekStart   As Date, _
    ws1         As Worksheet, _
    ws2         As Worksheet
 
Set ws1 = Sheets("Sheet1") 'Change this sheet name to fit your data
Set ws2 = Sheets("Sheet2") 'Change this sheet name to fit your data
WeekStart = Date - (Weekday(Date, 3))
ws1.Unprotect "PasswordText"
With ws1.Range("BS:BS")
    Set rng = .Find(WeekStart, LookIn:=xlValues)
    If Not rng Is Nothing Then
        rng1 = rng.Address
        Do
            ws2.Range("BS" & rng.Row).Value = rng.Value
            Set rng = .FindNext(rng)
        Loop While Not rng Is Nothing And rng.Address <> rng1
    End If
End With
End Sub

 

[Andrew Poulsom]

Have a look at the Find method in VBA Help. It includes an example of how to find all cells that meet a criterion. Also read this:

http://www.cpearson.com/Excel/DateTimeVBA.htm#Finding

for information about using Find with dates.

 

[Noz2k]

Thank you both.

Mr Kowz, I think I understand most of that. I'm a little unsure about the line 'ws2.Range("BS" & rng.Row).Value = rng.Value' I think that is posting in the same row on sheet 2 as the value was found in sheet1? Is that right?

What I want to do is paste specific values from the same row into different but specific cells on sheet2.

To do this I'm pretty sure I can just say for instance

ws2.Range("A1") = rng.Offset(, -19).Value
ws2.Range("C3") = rng.Offset(,-27).Value

etc

However when it loops I need all the row numbers in ws2 to be for instance +32 greater.

So on the 2nd instance the code would essentially become

ws2.Range("A33") = rng.Offset(, -19).Value
ws2.Range("C35") = rng.Offset(, -27).Value

 

[MrKowz]

Thank you for the more detailed explanation. Try:

Public Sub FindAllInstances()
Dim rng         As Range, _
    rng1        As String, _
    WeekStart   As Date, _
    ws1         As Worksheet, _
    ws2         As Worksheet, _
    findoffset  As Long
    
Set ws1 = Sheets("Sheet1") 'Change this sheet name to fit your data
Set ws2 = Sheets("Sheet2") 'Change this sheet name to fit your data
WeekStart = Date - (Weekday(Date, 3))
ws1.Unprotect "PasswordText"
findoffset = 0
With ws1.Range("BS:BS")
    Set rng = .Find(WeekStart, LookIn:=xlValues)
    If Not rng Is Nothing Then
        rng1 = rng.Address
        Do
            ws2.Range("A1").Offset(findoffset * 32, 0).Value = rng.Offset(0, -19).Value
            ws2.Range("C3").Offset(findoffset * 32, 0).Value = rng.Offset(0, -27).Value
            findoffset = findoffset + 1
            Set rng = .FindNext(rng)
        Loop While Not rng Is Nothing And rng.Address <> rng1
    End If
End With
End Sub

 

[Noz2k]

Thank you,

That looks like it's just what I am after. Hopefully I'm beginning to get a better understanding of using Dim's and Loops. So thanks again for the help.

I'm pretty sure I understand enough of that to amend it to fully fit my requirements.

 

[MrKowz]

Thank you,

That looks like it's just what I am after. Hopefully I'm beginning to get a better understanding of using Dim's and Loops. So thanks again for the help.

I'm pretty sure I understand enough of that to amend it to fully fit my requirements.

Great! Thanks for the feedback.

 

[SirSchiz]

If you all don't mind. May I ask to modify this a bit? I have been looking for weeks for something similar to this, and have struggled completing the script. I have to find a string "Dear". All data is in A1. Once the script finds "Dear", make that the active cell and select until the last used cell, copy that data to a sheet named "Sheet 2", and finally move to the next worksheet and loop. The workbook has about 600 worksheets.

The Icing on the cake would be to also name each pasted range the name of the worksheet it came from.

Thanks for any help you'd be willing to give.


Here's what I have so far:

Sub Search-Loopin()

	Dim varSerachTerm As String
	Dim c As Range
	
	varSearchTerm = InputBox("Enter Search Terms: ") 'Using inputbox to delcare this variable.
		
	Set c = .Find(What:="varSearchTerm", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= False, SearchFormat:=False).Activate
	   If Not c Is Nothing Then
	      firstaddress = c.Address
		Do
			'and now I'm lost. I can't seem to think what comes next.
			' I used the recorder to try and get some hints, but man. I'm Confused.
			' Here's what the recorder gave me: 
    
                        ' Cells.Find(What:="Dear", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
			':=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
			'False, SearchFormat:=False).Activate
			'ActiveCell.Select
			'Range(Selection, Selection.End(xlDown)).Select
			'Selection.Copy
			'Sheets("Sheet2").Select
		        'ActiveWindow.SmallScroll Down:=21
			'ActiveCell.Offset(34, 0).Range("A1").Select
			'ActiveSheet.Paste
		
	
	End Sub