Makro for finding dates and copy/paste content from below

rokandil

New Member
Joined
Jul 8, 2019
Messages
2
Hi I have this code which basically finds the date I need from a table and copies a certain number of cells below and pastes it somewhere else. My question is how I could amend it so that after the first round a question box appears which asks if more dates are needed. If yes again it should be possible to type in the date and number of cells but this time the content will be automatically placed one column right from the previous one.. so in the end I get a listing of selected data. Would be happy if you got a solution...

Thats the code so far:


Public Sub Date__Suchen()
Dim rngFind As Range
Dim strDate As String
Dim a As Long, b As Long

strDate = InputBox("Date:", , CDate(Date))
If strDate = "" Then Exit Sub

a = Sheets("Spreads").Cells(58, Columns.Count).End(xlToLeft).Column

Set rngFind = Sheets("Spreads").Range(Cells(58, 1), Cells(58, a)).Find(strDate, LookIn:= _
xlFormulas)
If Not rngFind Is Nothing Then
b = InputBox("How many rows needed?", "Rows", "1")
Range(Cells(rngFind.Row, rngFind.Column), Cells(rngFind.Row + b, rngFind.Column)).Copy
Sheets("Spreads2").Range("C95").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Else
MsgBox "Date not found!"
End If

End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,224,825
Messages
6,181,191
Members
453,021
Latest member
pingpong7117

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
Back
Top