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