VBA: Finding the row number by looking up a user defined value (date)
<p>I am trying to use VBA to look up the row value that corresponds to a user defined date on one of my work sheets so that I am able to edit all data on that row.</p>

<p>As a bit of context:</p>

<p>I have several time series data sets that all have different start and end dates with a good portion of overlap in the middle. I want to chart these using user defined date parameters, however, because of non-uniform start dates, the chart is impossible to dynamically rebase.</p>

<p>I was hoping to use a macro to clone the data on one sheet, overwrite the line of values that corresponds to the user defined start date, and then calculate return values based on percentage change figures (I already have in a different sheet).</p>

<p>If I can dynamically o/w the row that corresponds to the UD start date of the date range, I can replace it with a one and all my calculations will effectively rebase.</p>

<p>Any and all feedback would be great!</p>

<p>EDIT</p>

<p>Lucas,</p>

<p>I am having two issues; firstly, what I have inexpertly cobbled together doesn't work when I protect the sheets (not insurmountable); secondly, it doesn't work :). Here is my work:</p>

<pre><code>Sub Rebase()

Dim UDStartVal
Dim UDStartLoc As Range
Dim UDRow As Integer

'
' Rebase Macro
' A macro to rebase the chart to the user defined start date.
'

'
    Sheets("Cumulative Monthly Returns").Select
    Cells.Select
    Selection.Copy
    Sheets("Chart Numbers").Select
    Range("A1").Select
    ActiveSheet.Paste

' Lookup to change the value of the cells corresponding to the user defined start date to 0, effectivley rebasing the portfolo.

    Worksheets("Cumulative Period Returns").Activate
    UDStartVal = Cells(4, 2).Value

    Set UDStartLoc = Range("A:A").SpecialCells(xlCellTypeVisible).Find(UDStartVal)
    Set UDRow = UDStartLoc.Row


Stop


End Sub
</code></pre>