VBA Excel Loop for Multiple occurrence of a Value

KAHHAR

New Member
Joined
Feb 23, 2021
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
Create Loop for Copy data for Multiple occurrence of Sheets("Result").Range("D1").Text

VBA Code:
Private Sub CommandButton1_Click()
Dim celldesav As Range
Dim rdesav As Range
Dim searchdesav As String

Set rdesav = Sheets("List").Columns("A:A")
searchdesav = Sheets("Result").Range("D1").Text
Set celldesav = rdesav.Find(What:=searchdesav, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)

Sheets("List").Range(celldesav, celldesav.Offset(-4, 0)).Copy
Sheets("Result").Range("A1").PasteSpecial Paste:=xlPasteValues
Sheets("List").Range(celldesav, celldesav.Offset(1, 0)).Copy
Sheets("Result").Range("A1").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End Sub

Need a quick help please
 

Attachments

  • Sheet- List.png
    Sheet- List.png
    74 KB · Views: 9
  • Sheet- Result.png
    Sheet- Result.png
    71.8 KB · Views: 9

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

fadee2

Active Member
Joined
Nov 7, 2020
Messages
336
Office Version
  1. 2019
Platform
  1. Windows
Hi Kahhar,

Welcome to the board,

I would suggest using findnext in vba for searching multiple occurrences of a string, in a data set.
Complete how to is available here.

hth...
 

KAHHAR

New Member
Joined
Feb 23, 2021
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
Hi Kahhar,

Welcome to the board,

I would suggest using findnext in vba for searching multiple occurrences of a string, in a data set.
Complete how to is available here.

hth...
I'M new in codding. could you please describe me about looping my code.
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,061
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Private Sub CommandButton1_Click()
Dim celldesav As Range
Dim rdesav As Range
Dim searchdesav As String

Set rdesav = Sheets("List").Columns("A:A")
searchdesav = Sheets("Result").Range("D1").Text
Set celldesav = rdesav.Find(What:=searchdesav, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)

If Not celldesav Is Nothing Then
    FirstAddress = celldesav.Address
    Do
        Sheets("List").Range(celldesav.Offset(1, 0), celldesav.Offset(-4, 0)).Copy
        Sheets("Result").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
        Set celldesav = rdesav.FindNext(celldesav)
        if celldesav Is Nothing Then Goto DoneFinding
    Loop While celldesav.Address <> FirstAddress
DoneFinding:
End IF
End Sub
 
Solution

KAHHAR

New Member
Joined
Feb 23, 2021
Messages
5
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

thank@

mart37 it's working​

 

KAHHAR

New Member
Joined
Feb 23, 2021
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
VBA Code:
Private Sub CommandButton1_Click()
Dim celldesav As Range
Dim rdesav As Range
Dim searchdesav As String

Set rdesav = Sheets("List").Columns("A:A")
searchdesav = Sheets("Result").Range("D1").Text
Set celldesav = rdesav.Find(What:=searchdesav, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)

If Not celldesav Is Nothing Then
    FirstAddress = celldesav.Address
    Do
        Sheets("List").Range(celldesav.Offset(1, 0), celldesav.Offset(-4, 0)).Copy
        Sheets("Result").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
        Set celldesav = rdesav.FindNext(celldesav)
        if celldesav Is Nothing Then Goto DoneFinding
    Loop While celldesav.Address <> FirstAddress
DoneFinding:
End IF
End Sub
Can i use Greater then zero value for search instead of "Sheets("Result").Range("D1").Text"
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,061
Office Version
  1. 2016
Platform
  1. Windows
What do you mean? Greater than zero is almost everything.
Can you give an example what you want?
 

KAHHAR

New Member
Joined
Feb 23, 2021
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
What do you mean? Greater than zero is almost everything.
Can you give an example what you want?
Sorry to bother you. Your Code is working perfectly.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,663
Messages
5,626,163
Members
416,166
Latest member
Archimed

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top