Convert Dynamic Cell to Static when Date Changes

Not sure if this helps, try on a copy first.

Please Login or Register  to view this content.

A bit confused with "copy into any cells in Column C"

Hi John,

Thanks for your reply. Here's an example of what I'm trying to do. But in the meantime, I will try your suggestion.

Hi John, there's been a "Run Time Error 9 - Script out of Range"

I don't know how you'd get a subscript out of range unless there is something mispelled in your code.
However, this seems to do what I think you want.

Please Login or Register  to view this content.

I tested it on your sample.

Hi John,

A thought out of left field, can a copy and paste special, in VBA, achieve what I'm requesting in an effort to convert that volatile C Column cell into a static cell? If so, how would I write this? I'm okay with functions, but hopeless writing VBA.

Originally Posted by SteveG1965

Hi John,

Thanks for revisiting. L24 (Sheet8) is copying across to Column C (Sheet11) where the date in Column B (Sheet11) = F1 (Sheet8), but the value does not become static. Meaning, every time a new date is chosen in F1 (Sheet8), the previously updated cell in Column C (Sheet11) reverts back to zero, so that cell has not become static. Someone has suggested that what I'm trying to achieve, simply can't be done is that right?

PS: Still getting Run-Time Error '9':
Subscript out of Range

Maybe try

Please Login or Register  to view this content.

I can't duplicate the run time error. Based on the sample you provided, it works when tested.

That's fine, John.

Thanks for trying mate. I've decided on another plan of attack. I have created a new Macro which was working brilliantly until I tried to:

Copy Sheets("Sheet8").Range("L24") and pastespecial into Sheets("Sheet11")ActiveCell if result Sheets("Sheet11").Range("b:b")=Sheets("Sheet8").Range("F1").If False=0.

Any suggestions? Here's the sample code:

Private Sub CommandButton1_Click()
Dim strFind As String

Dim rngFound As Range
strFind = InputBox(prompt:="Enter Date (eg: 09jun)", Title:="Find what?")
If Len(strFind) > 0 Then
Set rngFound = Range("b:b").Find(what:=strFind, LookIn:=xlValues, lookat:=xlPart, MatchCase:=False).Offset(0, 1)
Sheets("Sheet8").Range("L24").Copy
ActiveCell.PasteSpecial Paste:=xlPasteValues.


If Not rngFound Is Nothing Then
Application.Goto rngFound, True

Exit Sub
End If
MsgBox strFind & " not found."
End If


End Sub

You're welcome. Glad to help out. Please comply with Forum Rule No. 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.